topic Re: data step merging - converting to proc sql help in SAS Programming

data step merging - converting to proc sql help

buddha_d — Mon, 23 Oct 2017 01:08:02 GMT

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;

Re: data step merging - converting to proc sql help

RahulG — Mon, 23 Oct 2017 03:57:48 GMT

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

Re: data step merging - converting to proc sql help

error_prone — Mon, 23 Oct 2017 03:44:32 GMT

@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.

Re: data step merging - converting to proc sql help

buddha_d — Mon, 23 Oct 2017 03:49:47 GMT

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.

Re: data step merging - converting to proc sql help

buddha_d — Mon, 23 Oct 2017 03:50:21 GMT

thanks error_prone for the suggestion

Re: data step merging - converting to proc sql help

Reeza — Mon, 23 Oct 2017 03:55:01 GMT

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.

Re: data step merging - converting to proc sql help

buddha_d — Mon, 23 Oct 2017 04:10:26 GMT

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