topic data step equivalent of SQL left join in SAS Programming

data step equivalent of SQL left join

SAS_inquisitive — Sun, 12 Feb 2017 15:42:05 GMT

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;

Re: data step equivalent of SQL left join

art297 — Sun, 12 Feb 2017 16:41:32 GMT

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

Re: data step equivalent of SQL left join

mkeintz — Sun, 12 Feb 2017 16:46:24 GMT

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;

Re: data step equivalent of SQL left join

LinusH — Sun, 12 Feb 2017 20:34:19 GMT

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.