Proc SQL equiv of Merge (If A or B)

wer61537 · Posted 03-09-2016 11:04 PM

I wonder if a full outer join in proc sql is the equivalent of a merge using if (A or B).

DATA All;
MERGE West(IN=A)
East(IN=B);
BY Id;
IF A OR B;
RUN;

Reeza · Posted 03-09-2016 11:25 PM

Depends on if you have multiple instances of your BY variables.

There's several good write ups on data step vs sql joins - search on lexjansen.com

http://www2.sas.com/proceedings/sugi30/249-30.pdf

Kurt_Bremser · Posted 03-10-2016 02:09 AM

SQL will give you the cartesian product, while the data step will give you as many records for a given by value as were present in one of the input datasets.

example:

dataset a has 2 records for x = 1, while dataset b has 3.

SQL (join on a.x = b.x) will give you six records (all combinations), the data step merge (by x; with if a or b;) 3.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

RW9 · Posted 03-10-2016 04:24 AM

Here is a nice pictorial of SQL joins:

Proc SQL equiv of Merge (If A or B)

Re: Proc SQL equiv of Merge (If A or B)

Re: Proc SQL equiv of Merge (If A or B)

Re: Proc SQL equiv of Merge (If A or B)

Proc SQL equiv of Merge (If A or B)

Re: Proc SQL equiv of Merge (If A or B)

Re: Proc SQL equiv of Merge (If A or B)

Re: Proc SQL equiv of Merge (If A or B)

Click image to register for webinar

Classroom Training Available!