Help using Base SAS procedures

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

Reply
Contributor
Posts: 25

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

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
PROC Star
Posts: 1,558

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

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.
Ask a Question
Discussion stats
  • 1 reply
  • 184 views
  • 0 likes
  • 2 in conversation