Desktop productivity for business analysts and programmers

Converting SAS code to SQL

Reply
Contributor
Posts: 25

Converting SAS code to SQL

Hello Team,

 

I am new to SAS and looking for converting below code to SQL 

 

data strat.master;
merge strat.master1(in=ge) strat.client strat.abc(in=pw);
by code;
if ge;
abc=pw;
run;

 

I know that Merge by is match merging but not getting meaning of last 3 lines "if ge; abc=pw"

 

Your help is much appreciated

 

Thanks In advance

Super User
Posts: 19,038

Re: Converting SAS code to SQL

Note the IN on the merge statement that creates two automatic variables, ge and pw. 

 

The variables are indicators that shownthe source of the record. 

 

If ge -> record is present in indicated dataset. This would be analogous to specifying a right/left join. 

 

Abc=pw -> store an indicator variable that shows if the record was present in the second dataset. 

You can replicate this functionality using a Case statement in your query. 

Frequent Contributor
Posts: 123

Re: Converting SAS code to SQL

PROC SQL;
  CREATE TABLE strat.master AS
  SELECT *
  FROM strat.master sm left join strat.client sc (on sm.code=sc.code) left join strat.abc ab (on sm.code=ab.code);
QUIT;

Maybe start out with something like this. 

Super User
Posts: 11,105

Re: Converting SAS code to SQL

Don't forget that Merge treats same named variables in multiple datasets very differently than a simple join in SQL would.

Super User
Posts: 9,856

Re: Converting SAS code to SQL

Assuming CODE in all these three tables has no duplicated value. i.e. its value is unique.

 

data master1;
 set sashelp.class;
 rename name=code;
run;

data client ;
 set sashelp.class;
 if _n_=8 then stop;
 rename name=code age=new_age1;
 keep name age;
run;

data abc;
 set sashelp.class;
 if _n_=4 then stop;
 rename name=code age=new_age2;
 keep name age;
run;


proc sql;
select a.*,b.new_age1,c.new_age2,not missing(c.code) as abc
 from master1 as a left join client as b on a.code=b.code
   left join abc as c on a.code=c.code;
quit;
Ask a Question
Discussion stats
  • 4 replies
  • 246 views
  • 0 likes
  • 5 in conversation