title: SAS proc sql left join to hash object
body: <p>I'm new to hash objects, but I'd like to learn more about them. I'm trying to find ways to substitute  proc sql  with hash object. I have to two tables, when i have a proc sql with inner join and an equal with hash object it works, but when i have a proc sql with left join i don´t know how to make in hash object. Thankyou very much. Sorry by my English.</p>
  <p>Table01.</p>
  <pre><code>data Principal;
     input idd $ name $ Apellid1 $ valor $;
     datalines;
      1977 Arthur Pendrag 0001
      1978 Minerva  Athena 0001
      2011 Noe Arca 0001
      2014 Thor Hammer 0001
      0001 Seiya Pegaso 0001
      0002 Ikki Fenix 0001
      0003 Shun Andromeda 0001
      0004 Shiryu Dragon 0001
      0005 Yoga Cisne 0001
      0006 Naruto Konoha 0001
      0007 Sasuke Kun 0001
  ;
  </code></pre>
  <p>Table02</p>
  <pre><code>data Secundarea;
     input idd $ Apellid2 $ mival $;
     datalines;
      1977 Excalibu 0003
      1978 Atenea 0004
      2011 Biblia 0005
      2014 Odin 0006
      0001 Sagigario 0007
      0002 Virgo 0008
      0003 Piscis 0009
      0004 Libra 0010
      0005 Acuario 0011
      0008 Aries 0012
  ;
  </code></pre>
  <p>Proc sql inner join</p>
  <pre class="lang-sql prettyprint-override"><code>proc sql;
  create table sqlinner as
  select *
  from principal as p inner join secundarea as s
  on p.idd=s.idd;
  quit;
  </code></pre>
  <p>Hash object (inner join) it works</p>
  <pre><code>data mihashInner;
  declare hash h();
  h.defineKey('IDD');
  h.defineData ('IDD','APELLID2','MIVAL');
  h.defineDone();
  do until(fin1);
      set SECUNDAREA  end=fin1;
      h.add();
  end;
  do until (fin2);
      set PRINCIPAL end=fin2;
     if h.find()=0 then
      output;
  end;    
  run;
  </code></pre>
  <p>Proc sql (left join)</p>
  <pre class="lang-sql prettyprint-override"><code>proc sql;
  create table sqlleft as
  select *
  from principal as p left join secundarea as s
  on p.idd=s.idd;
  quit;
  </code></pre>
  <p>How to make in hash object? I´m trying two ways.</p>
  <pre><code>data mihashLeft2;
  declare hash h();
  h.defineKey('IDD');
  h.defineData ('IDD','APELLID2','MIVAL');
  h.defineDone();
  do until(fin1);
      set SECUNDAREA  end=fin1;
      h.add();
  end;
  do until (fin2);
      set PRINCIPAL end=fin2;
          rc=h.find();
      output;
  end;
  
  run;
  </code></pre>
  <p>Or this. But nothing. Thx.</p>
  <pre><code>data mihashLeft;
  if 0 then set SECUNDAREA;
  if _n_ =1 then do;
      declare hash hhh(dataset: 'SECUNDAREA', multidata:'y');
      hhh.DefineKey('IDD');
      hhh.DefineData('IDD','APELLID2','MIVAL');
      hhh.DefineDone();
     
      set PRINCIPAL;
      rc = hhh.find();
          if rc ne 0 then do;
              call missing(MIVAL);
              output;
          end;
          else
          do while(rc = 0);
              output;
              rc = hhh.find_next();
          end;
          end;
          run;
  </code></pre>