CODEWITHSUNDEEP
mergesas
hubertsng
hubertsng

Reputation: 479

Merging datasets only if they exist

So I'm trying to create a macro in sas and I'm attempting to merge multiple data sets in one data step. This macro also creates a variety of different data sets dynamically so I have no idea what data sets are going to be created and which ones aren't going to. I'm trying to merge four data sets in one data step and I'm trying to only merge the ones that exist and don't merge the ones that don't.

Haven't really tried anything but what I'm trying to do kind of be seen below.

DATA Something;
    MERGE Something SomethingElse AnotherThing EXIST(YetAnotherThing)*YetAnotherThing;
RUN;

Well obviously that doesn't work because SAS doesn't work like that but I'm trying to do something like that where YetAnotherThing is one of the data sets that I am testing to see whether or not it exists and to merge it to Something if it does.

Upvotes: 0

Views: 1659

Answers (3)

Reeza
Reeza

Reputation: 21274

If you have a systematic naming convention this can be simplified. For example if you have a common prefix it becomes:

data want;
merge prefix: ;
run;

If they're all in the same library it's also easy. But otherwise you're stuck checking every single name as above.

Upvotes: 2

Ben
Ben

Reputation: 489

Something along these lines:

data test1;
do i = 1 to 10;
    val1 = i;
    output;
end;
run;

data test2;
do i = 1 to 10;
    val2 = i*2;
    output;
end;
run;

data test3;
do i = 1 to 10;
    val3 = i*3;
    output;
end;
run;

data test5;
do i = 1 to 10;
    val5 = i*4;
    output;
end;
run;



%macro multi_merge(varlist);
%local j;
data test_merge;
set %scan(&varlist,1);
run;
%put %sysfunc(countw(&varlist));
%if  %sysfunc(countw(&varlist)) > 1 %then %do;
    %do j = 2 %to %sysfunc(countw(&varlist));
        %if %sysfunc(exist(%scan(&varlist,&j))) %then %do;
            data test_merge;
            merge test_merge %scan(&varlist,&j);
            by i;
            run;
        %end;
    %end;
%end;
%mend;

%multi_merge(test1 test2 test3 test4 test5);

Test4 does not exist.

Same thing with no loop:

if you don't want to loop, you can do this:

%macro if_exists(varlist);
%if %sysfunc(exist(%scan(&varlist,1))) %then %scan(&varlist,1);
%mend;

data test_merge2;
merge test1
%if_exists(test2)
%if_exists(test3)
%if_exists(test4)
%if_exists(test5)
%if_exists(test6);
by i;
run;

Upvotes: 1

user667489
user667489

Reputation: 9569

I can think of two options:

  1. Loop through the list of input datasets, check if each exists, then merge only those that do.
  2. At the start of your macro, before you conditionally create each of the potential input datasets, create a dummy dataset with the same name containing no rows or columns. Then when you attempt to merge them, they will always exist, without messing up the output with lots of missing values.

Sample code for creating an empty dataset:

data want;
stop;
run;

Upvotes: 0

Related Questions