topic Re: Converting SAS code to SQL in SAS Enterprise Guide

Converting SAS code to SQL

yudhishtirb — Thu, 02 Jun 2016 13:14:54 GMT

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

Re: Converting SAS code to SQL

Reeza — Thu, 02 Jun 2016 13:57:17 GMT

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.

Re: Converting SAS code to SQL

JediApprentice — Thu, 02 Jun 2016 17:10:20 GMT

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.

Re: Converting SAS code to SQL

ballardw — Thu, 02 Jun 2016 18:01:35 GMT

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

Re: Converting SAS code to SQL

Ksharp — Fri, 03 Jun 2016 01:52:41 GMT

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;