topic Re: Proc SQL equiv of Merge (If A or B) in SAS Procedures

Proc SQL equiv of Merge (If A or B)

wer61537 — Thu, 10 Mar 2016 04:04:58 GMT

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;

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

Reeza — Thu, 10 Mar 2016 04:25:02 GMT

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

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

Kurt_Bremser — Thu, 10 Mar 2016 07:09:31 GMT

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.

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

RW9 — Thu, 10 Mar 2016 09:24:01 GMT

Here is a nice pictorial of SQL joins: