DATA Step, Macro, Functions and more

data step equivalent of SQL left join

Reply
Regular Contributor
Posts: 234

data step equivalent of SQL left join

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;

 

 

 

PROC Star
Posts: 7,356

Re: data step equivalent of SQL left join

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

 

Valued Guide
Posts: 797

Re: data step equivalent of SQL left join

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;

Super User
Posts: 5,255

Re: data step equivalent of SQL left join

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.

Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 296 views
  • 2 likes
  • 4 in conversation