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: 19,815

Re: Merge to SQL

Posted in reply to rakeshvvv

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: 19,815

Re: Merge to SQL

Posted in reply to rakeshvvv

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

Re: Merge to SQL

Posted in reply to rakeshvvv

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

Data never sleeps
Super User
Posts: 5,511

Re: Merge to SQL

Posted in reply to rakeshvvv

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: 19,815

Re: Merge to SQL

Posted in reply to rakeshvvv

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
  • 231 views
  • 0 likes
  • 4 in conversation