topic Re: merge data sets when some of them are not existing in SAS Programming

merge data sets when some of them are not existing

Ronein — Mon, 01 Feb 2021 13:14:20 GMT

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;

Re: merge data sets when some of them are not existing

Tom — Mon, 01 Feb 2021 13:24:06 GMT

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.

Re: merge data sets when some of them are not existing

SASJedi — Mon, 01 Feb 2021 19:22:09 GMT

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;

Re: merge data sets when some of them are not existing

ChrisNZ — Tue, 02 Feb 2021 21:45:13 GMT

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]

Re: merge data sets when some of them are not existing

SASJedi — Tue, 02 Feb 2021 17:32:36 GMT

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

Re: merge data sets when some of them are not existing

Tom — Tue, 02 Feb 2021 17:57:00 GMT

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.

Re: merge data sets when some of them are not existing

SASJedi — Mon, 08 Feb 2021 14:03:08 GMT

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

Re: merge data sets when some of them are not existing

Tom — Mon, 08 Feb 2021 15:37:31 GMT

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