data step equivalent of SQL left join

[SAS_inquisitive]

Hello,

Does this data step code represent sql equivalent below?

data want;

   merge a (in = a) b (in = b) c (in = c);

   by id;

   if a or b;

run;

proc sql;

   create table want as

   select a*,b*,c*

     from a

        left join b on a.id = b.id

        left join c on b.id = c.id;

quit;

[art297]

No. If it isn't a many to many merge/join then I think the following would be the same:

data want1;
   merge a (in = a) b (in = b) c (in = c);
   by id;
   if a and (a or b);
run;
 
proc sql;
   create table want2 as
   select a.*,b.*,c.*
     from a
        left join b on a.id = b.id
        left join c on b.id = c.id
  ;
quit;

HTH,

Art, CEO, AnalystFinder.com

[mkeintz]

The short answer is no.  But SAS is a data analysis system, and your question is an excellent opportunity to use it. Here is such a test:

data a b c;

  do id=1 to 2;

    output a b c;

    output b c;

    output c;

  end;

run;

data mergeabc;

  merge a b c;

  by id;

run;

proc sql;

  create table want as

  select a.*,b.*,c.*

  from a

    left join b on a.id = b.id

    left join c on b.id = c.id;

quit;

[LinusH]

Why do you ask?

And, strictly, you never be sure if a SQL query and a data step have the same result. It all comes down to the data at hand, and what your desired outcome is. SO I think the general answer to all SQL vs data step question should be no. On of the most obvious things is that SQL does not preserve the original sort order.