BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buddha_d
Pyrite | Level 9

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RahulG
Barite | Level 11

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

6 REPLIES 6
RahulG
Barite | Level 11

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

buddha_d
Pyrite | Level 9

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. 

error_prone
Barite | Level 11
@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.
buddha_d
Pyrite | Level 9
thanks error_prone for the suggestion
Reeza
Super User

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.  

 

 

buddha_d
Pyrite | Level 9

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

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
  • 6 replies
  • 1309 views
  • 1 like
  • 4 in conversation