topic Re: Merge in proc sql when some of data sets are not exisiting in SAS Programming

Merge in proc sql when some of data sets are not exisiting

Ronein — Tue, 02 Feb 2021 09:27:19 GMT

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;

Re: Merge in proc sql when some of data sets are not exisiting

yabwon — Tue, 02 Feb 2021 10:04:10 GMT

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

Re: Merge in proc sql when some of data sets are not exisiting

Ronein — Tue, 02 Feb 2021 12:35:29 GMT

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

Re: Merge in proc sql when some of data sets are not exisiting

PaigeMiller — Tue, 02 Feb 2021 12:40:51 GMT

@Ronein wrote:
Thank you but I am looking for same solution as in data step but with proc sql.

Then use a DATA step merge. Maxim 14: Use the right tool.

Don't assume that SQL does everything identically to DATA step merge.

Re: Merge in proc sql when some of data sets are not exisiting

Tom — Tue, 02 Feb 2021 17:02:15 GMT

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