Full Join (SQL) ---- Merge (SAS)???

Alankar — Wed, 28 Apr 2010 03:06:17 GMT

Here we have an SQL full join code in SAS :

proc sql;
create table test as select * from prod1 full join prod2 on (prod1.fund = prod2.fund);
quit;

how can i write the same code by using Merge Statement in SAS Data Step Programming?

data test;
merge prod1 (in = a)
prod1 (in = b);
by fund;
??????????
run;

Thanks in Advance.
- Alankar

Re: Full Join (SQL) ---- Merge (SAS)???

ChrisNZ — Wed, 28 Apr 2010 03:35:41 GMT

You don't need any additional statements, merge by does a full join by default.

merge by does however require that tables/views you merge be sorted or indexed by the merge keys before-hand, whereas sql can sort (or do other things) on the fly if needed.

One case where the results will be different between merge by and sql are if you have non-unique keys in both tables as merge by does not do cartesian products.

Another case is if you have identically named variables, as sql will keep the values from the first table, whereas merge by will overwrite them with the second table's values.

topic Full Join (SQL) ---- Merge (SAS)??? in SAS Procedures

Full Join (SQL) ---- Merge (SAS)???

Re: Full Join (SQL) ---- Merge (SAS)???