Merge to SQL

[rakeshvvv]

Can some one give me a proc sql code for the following query in sas

PROC SORT DATA=x  OUT=x;

      BY a;

RUN;

PROC SORT DATA=z  OUT=z (KEEP = a b c);

      BY a;

RUN;

DATA  final;

      MERGE  x(IN=A) z;

      BY a;

      IF A;

RUN;

[Reeza]

Use a LEFT JOIN

[rakeshvvv]

I am not sure about the syntax for LEFT JOIN...SO POSTED here...

[LinusH]

Have you tried Google, or support.sas.com?

[Astounding]

This program is like making mud pies ... you might have fun experimenting with it, but you would never use it.  There is no practical use for a program that uses a BY variable and an IN= variable having the same name.

Good luck.

[Reeza · Accepted solution]

Assuming that the duplicate variable of A and in=A is an accident, see page 4 here:

http://support.sas.com/resources/papers/proceedings09/035-2009.pdf

There is some default behaviour that is not replicable between the data step/sql step such as how they treat the merge if the two data sets have the same variable.

proc sql;

create table want as

select a.*, b.*

from a

left join b

on a.var1=b.var1

order by var1;

quit;