BookmarkSubscribeRSS Feed
yudhishtirb
Calcite | Level 5

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

4 REPLIES 4
Reeza
Super User

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. 

JediApprentice
Pyrite | Level 9
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. 

ballardw
Super User

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 877 views
  • 0 likes
  • 5 in conversation