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;
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
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
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.
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.
Thanks Reeza for valuable suggestion. I will try to see if this works as well.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.