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
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.
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.
Don't forget that Merge treats same named variables in multiple datasets very differently than a simple join in SQL would.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.