DATA Step, Macro, Functions and more

data step merging - converting to proc sql help

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 100
Accepted Solution

data step merging - converting to proc sql help

[ Edited ]

SAS gurus, I need help with proc sql code. I have to merge 4 datasets and I have the following code in datastep. I heard that proc sql doesn't require sorting step. If that is the case then I think I can put below 4 data steps in a single proc sql code. I underlined and italicized the steps I did.

Can any one help me with this please. Thanks in advance !!!!

 

data temp;

merge data1 (in=ina) data2(in=inb);

by id regiment_number serial_number;

if ina ;

run;

1.sort data temp by lab_grade

2.sort data temp1 by lab_grade then merge with temp.

 

data temp2 ;

merge temp (in=ina) temp1 (in=inb);

by lab_grade ;

if ina and not inb then lab_grade='N/A';

if ina then output;

run;

3.I need to merge temp2 (commercial_grade) dataset to temp3 with commercial_grade. I sorted temp2 and temp3 with commercial_grade.

 

data temp4 ;

merge temp2 (in=ina) temp3 (in=inb);

by commercial_grade ;

if ina and not inb then commercial_grade='N/A';

if ina then output;

run;


Accepted Solutions
Solution
‎10-23-2017 12:10 AM
Super Contributor
Posts: 266

Re: data step merging - converting to proc sql help

[ Edited ]

I do not know all the column names of data1, data2, temp1 and temp3 so I have to assume that 

commercial_grade and lab_grade also present in data1  table.

 

You also need to correct the select clause,enter the column name in select clause as you needed in final table temp4

 

proc sql;
create table temp4 as
select a.*,b.*,
case when c.lab_grade is null then 'N/A' else c.lab_grade end as lab_grade,
case when d.commercial_grade is null then 'N/A' else d.commercial_grade end as commercial_grade
from data1 a left join data2 b on a.id=b.id and a.regiment_number=b.regiment_number and a.serial_number=b.serial_number
left join temp1 c on a.lab_grade= c.lab_grade
left join temp3 d on a.commercial_grade = d.commerical_grade;
quit;

 

EDIT: Added comma after lab_grade - PG

View solution in original post


All Replies
Solution
‎10-23-2017 12:10 AM
Super Contributor
Posts: 266

Re: data step merging - converting to proc sql help

[ Edited ]

I do not know all the column names of data1, data2, temp1 and temp3 so I have to assume that 

commercial_grade and lab_grade also present in data1  table.

 

You also need to correct the select clause,enter the column name in select clause as you needed in final table temp4

 

proc sql;
create table temp4 as
select a.*,b.*,
case when c.lab_grade is null then 'N/A' else c.lab_grade end as lab_grade,
case when d.commercial_grade is null then 'N/A' else d.commercial_grade end as commercial_grade
from data1 a left join data2 b on a.id=b.id and a.regiment_number=b.regiment_number and a.serial_number=b.serial_number
left join temp1 c on a.lab_grade= c.lab_grade
left join temp3 d on a.commercial_grade = d.commerical_grade;
quit;

 

EDIT: Added comma after lab_grade - PG

Frequent Contributor
Posts: 100

Re: data step merging - converting to proc sql help

Rahul, 

          I am sorry that I wasn't very clear in my previous post. lab_grade and commercial_grade are in data2 (not data1). I supposed the code might change? correct me if I am wrong? 

Thank you so much for taking time to write me. 

Frequent Contributor
Posts: 149

Re: data step merging - converting to proc sql help

@buddha_d just because you don't have to sort the datasets does not mean, that sas does not sort them when you use proc sql.
The proc sql posted by @RahulG may solve the problem, but it is less easy to read (at least for me at 5:42 am) and by far less easy to debug.
Frequent Contributor
Posts: 100

Re: data step merging - converting to proc sql help

Posted in reply to error_prone
thanks error_prone for the suggestion
Super User
Posts: 19,878

Re: data step merging - converting to proc sql help

Write your SQL steps one by one. 

First determine the type of join needed, one to one, one to many and then if its a left/right/inner join. Do that for each table first. Drawing it out is the easiest way. 

 

If you have access to EG, using Query Builder is a good way to visualize it. Start with replicating your first proc, make sure the results are the same and then move on to the next.  

 

 

Frequent Contributor
Posts: 100

Re: data step merging - converting to proc sql help

Thanks Reeza for valuable suggestion. I will try to see if this works as well.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 171 views
  • 1 like
  • 4 in conversation