DATA Step, Macro, Functions and more

data step equivalent of SQL left join

Reply
Super Contributor
Posts: 271

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,487

Re: data step equivalent of SQL left join

Posted in reply to SAS_inquisitive

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

 

Trusted Advisor
Posts: 1,022

Re: data step equivalent of SQL left join

Posted in reply to SAS_inquisitive

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,435

Re: data step equivalent of SQL left join

Posted in reply to SAS_inquisitive

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
  • 462 views
  • 2 likes
  • 4 in conversation