DATA Step, Macro, Functions and more

merge to sql conversion

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

merge to sql conversion

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;


Accepted Solutions
Solution
‎11-05-2017 09:11 AM
Super User
Posts: 2,512

Re: merge to sql conversion

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;

 

View solution in original post


All Replies
Solution
‎11-05-2017 09:11 AM
Super User
Posts: 2,512

Re: merge to sql conversion

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 123 views
  • 1 like
  • 2 in conversation