topic Re: Merge to SQL in SAS Procedures

Merge to SQL

rakeshvvv — Tue, 02 Sep 2014 13:30:00 GMT

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;

Re: Merge to SQL

Reeza — Tue, 02 Sep 2014 13:32:19 GMT

Use a LEFT JOIN

Re: Merge to SQL

rakeshvvv — Tue, 02 Sep 2014 13:37:45 GMT

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

Re: Merge to SQL

LinusH — Tue, 02 Sep 2014 13:40:18 GMT

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

Re: Merge to SQL

Astounding — Tue, 02 Sep 2014 13:49:18 GMT

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.

Re: Merge to SQL

Reeza — Tue, 02 Sep 2014 13:56:24 GMT

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;