Hello
I know how to merge data sets in data step .
I know how to dealt with situation when some of the data sets are not existing.
My question:
What i the way to merge via proc sql when some of the data sets are not exisiting?
data a;
input id x;
cards;
1 10
2 20
;
run;
data b;
input id w;
cards;
1 12
2 23
;
run;
data c;
input id y;
cards;
1 30
2 50
;
run;
%macro if_exists(dataset);
%if %sysfunc(exist(&dataset)) %then &dataset ;
%mend if_exists;
/**Way1-working well**/
data wanted1;
merge
%if_exists(a)
%if_exists(b)
%if_exists(c)
%if_exists(d)
%if_exists(e)
%if_exists(f)
;
by id;
run;
/**Way2-How can I do it??**/
PROC SQL;
create table wanted2 as
select
from a
left join b
on a.id=b.id
left join c
on a.id=c.id
left join d
on a.id=d.id
left join e
on a.id=e.id
left join f
on a.id=f.id
;
QUIT;
I would try with something like this:
%macro if_existsSQL(dataset, base=a, id=id);
%if %sysfunc(exist(&dataset)) %then
%do;
left join &dataset.(rename=(&id.=&id._&dataset.))
on &base..&id.=&dataset..&id._&dataset.
%end;
%mend if_existsSQL;
PROC SQL;
create table wanted2(drop=id_:) as
select *
from a
%if_existsSQL(b)
%if_existsSQL(c)
%if_existsSQL(d)
%if_existsSQL(e)
%if_existsSQL(f)
;
QUIT;
proc print;
run;
but, this SQL is not the same as merge is. You have left joins here and they are not equivalent to merge. You have to have the "right side" dataset to perform left join on it and merge doesn't care about it.
The renaming part is to avoid this: https://support.sas.com/kb/9/963.html warning message. I assume that all other variables names do not interfere.
All the best
Bart
@Ronein wrote:
Thank you but I am looking for same solution as in data step but with proc sql.
It means that I want to perform same merge as in data step with sql method
There is no easy SQL that replicates the data step MERGE statement.
The way the observations are combined is different when any of the dataset have multiple observations per BY group. Which dataset's value is used when you have non-key common variables is different.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.