merge data sets when some of them are not existing

[Ronein]

Hello

I want to merge data sets and sometimes some of them are not exisiting.

What is the way that there will not be an error

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;

 
data wanted;
merge
%if_exits(a)
%if_exits(b)
%if_exits(c)
%if_exits(d)
%if_exits(e)
;
by id;
run;

[Tom]

Are you asking how to create the %IF_EXIST() macro?

%macro if_exists(dataset);
%if %sysfunc(exist(&dataset)) %then &dataset ;
%mend if_exists;

But watch out if none of them exist.  If you a MERGE statement with no datasets specified. Like:

data wanted;
  merge ;
  by id;
run;

Then rather than doing nothing it will use the dataset named in the &SYSLAST macro variable. In other words the last created dataset.

[SASJedi]

You need to use %QSYSFUNC to execute the EXIST function for this. For example:

data a;
input id x;
cards;
1 10
2 20
;
run;

data b;
input id w;
cards;
1 12
2 23
;
run;


data d;
input id y;
cards;
1 30
2 50
;
run;

 
data wanted;
merge
%if %qsysfunc(exist(a)) %then %do; a %end;
%if %qsysfunc(exist(b)) %then %do; b %end;
%if %qsysfunc(exist(c)) %then %do; c %end;
%if %qsysfunc(exist(d)) %then %do; d %end;
;
by id;
run;

[ChrisNZ]

If you have an older sas release:

data WANT;
  merge %sysfunc(ifc(%sysfunc(exist(A)),A,))
        %sysfunc(ifc(%sysfunc(exist(B)),B,))
        %sysfunc(ifc(%sysfunc(exist(C)),C,));
  by ID;
run;

[Edited as I had left out one %sysfunc( call]  

[SASJedi]

So true, @ChrisNZ - versions of SAS before SAS 9.4M5 did not allow conditional macro statements in open code.

[Tom]

It seems silly to use %QSYSFUNC() instead of %SYSFUNC() to call a function that only returns 0 or 1. There is no reason to add macro quoting to that result.

[SASJedi]

You say silly, I say prudent. Potato, poTAHto.... In my 25 years of macro programming, I've expended many manhours troubleshooting my programs (an other people's programs) only to find that the root problem was a failure to quote text resolved from a macro process. There is no reason that a macro process should produce unquoted text unless the text being produced is executable macro code. So over the years, I've developed the habit of quoting all text produced by my macro processes unless I to execute it. And it's proved to be an excellent habit. It has significanlty reduced programming bugs, with a very satisfactory correlated reduction in debugging time. I dont intend to be precriptive here - feel free to use SYSFUNC if you prefer. But since I didn't intend to execut the result produced by the SAS function, I chose QSYSFUNC, and I stand by my choice...   

[Tom]

@SASJedi  Usually I find that debugging errors caused by not adding macro quoting is easier. They typically fail in spectacular ways. It is strange behavior when macro quoting is added in the wrong places that are usually more difficult to figure out as the generated SAS code looks valid, but the macro quoting causes it be interpreted incorrectly.