topic merge to sql conversion in SAS Programming

merge to sql conversion

GunnerEP — Sun, 29 Oct 2017 15:14:24 GMT

I have a merge statement which needs to be converted to a proc sql join,

data a.main;
merge main
a.proxy;
by id;
run;

which i wrote it as,

proc sql;
create table a.main as
select * from main a join a.proxy b
on a.id=b.id;
run;

Now the tough part for me is, the data step has gone bit complex with parameters how do i replicate the same in proc sql ?

data a.main;
merge main a.proxy
(drop=name address city pin_code
%if %eval(&ht2. IN XX YY ZZ) %then
%str(orig_name);
);
by id; run;
run;

Re: merge to sql conversion

ChrisNZ — Mon, 30 Oct 2017 02:36:41 GMT

1- Your merge and join steps are not equivalent. See below:

data T1; 
  X=1;
data T2; 
  X=2;
data J1; 
  merge T1 T2; 
  by X;
proc sql; 
  create table J2 as 
  select * 
  from T1 join T2 
   on T1.X=T2.X;
quit;

The SQL step only keeps common keys.

The steps are only equivalent if all key values are common to both tables, and unique

2- Just add the same option in your SQL.

proc sql;
create table a.main as
select * from main a join a.proxy(drop=name address city pin_code %if %eval(&ht2. IN XX YY ZZ) %then %str(orig_name) ) b
on a.id=b.id;
run;