Help using Base SAS procedures

Merge to SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

Merge to SQL

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;


Accepted Solutions
Solution
‎09-02-2014 09:56 AM
Super User
Posts: 17,963

Re: Merge to SQL

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;

View solution in original post


All Replies
Super User
Posts: 17,963

Re: Merge to SQL

Use a LEFT JOIN

Frequent Contributor
Posts: 145

Re: Merge to SQL

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

Super User
Posts: 5,260

Re: Merge to SQL

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

Data never sleeps
Super User
Posts: 5,099

Re: Merge to SQL

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.


Solution
‎09-02-2014 09:56 AM
Super User
Posts: 17,963

Re: Merge to SQL

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 222 views
  • 0 likes
  • 4 in conversation