topic Re: Merge data sets that some of them doesnt exist in SAS Programming

Merge data sets that some of them doesnt exist

Ronein — Fri, 18 Sep 2020 10:36:29 GMT

Hello

Let's say that in my work I receive a list of data sets from my manager and he ask me to Join them (Full join) By customer ID.

Let's say that some of the data sets in the list are not existing (manager made mistake and sent some data sets that are not existing).

One way to work is to check if each data set exist and then merge the data sets that exists .

I am looking for another solution that merge the all data sets and if one of the data sets doesn't exist then I will not get error.

What is the way to do it please?

Please find example

%let  DatasetsList=Rev2019 Rev2018 Rev2017;
/*List of data sets that my manager sent me */
/*In real world it contains 50 data sets*/

Data Rev2019 ;
input ID Rev;
cards;
1 10
2 20
;
run;

Data Rev2018 ;
input ID Rev;
cards;
1 15
2 25
;
run;

Data wanted;
merge %list;
by ID;
Run;
/*data set Rev2018  doesn't exist so I get error*/

Re: Merge data sets that some of them doesnt exist

PaigeMiller — Fri, 18 Sep 2020 11:11:10 GMT

You would need a macro loop to test if each data set in &datasetslist exists.

Something like this:

%let DatasetsList=Rev2019 Rev2018 Rev2017;

%macro dothis;
data wanted;
    merge 
        %do i=1 %to %sysfunc(countw(&datasetslist));
             %let this_ds=%scan(&datasetslist,&i,%str( ));
             %if %sysfunc(exist(&this_ds)) %then &this_ds;
        %end;
    ;
    by id;
run;
%mend;
%dothis

This code probably still fails if ALL of the data sets do not exist.

Re: Merge data sets that some of them doesnt exist

andreas_lds — Fri, 18 Sep 2020 12:05:04 GMT

Why do have to use a macro-variable? Are there datasets in the library whose name also begins with "rev"? If not you could use

data want;
  merge work.Rev:;
  by Id;
run;

Re: Merge data sets that some of them doesnt exist

gamotte — Fri, 18 Sep 2020 12:22:01 GMT

Hello,

If the case where no dataset exist is possible, you can try the following :

%macro merge_revs(start, end);

    data _NULL_;
        length datastep $100.;
        datastep='data want; merge';
        do year=&end. to &start. by -1;
            dsname=cats('Rev', year);
            if exist(dsname) then do;
                ok=1;
                call catx(' ', datastep, dsname);
            end;
        end;

    if ok then call execute(cats(datastep,'; by id; run;'));
    else put "No dataset for the given time range";
    run;

%mend;

%merge_revs(2017,  2020)

%merge_revs(2014, 2017)

Re: Merge data sets that some of them doesnt exist

gamotte — Fri, 18 Sep 2020 14:25:38 GMT

If no dataset in &Datasetslist exists, the merge instruction will be equivalent to

set &syslast.;

that is, the wanted dataset will be a copy of the last processed dataset if it contains a column "id"

or, if not, the program will raise an error.

Re: Merge data sets that some of them doesnt exist

PaigeMiller — Fri, 18 Sep 2020 16:53:37 GMT

@andreas_lds wrote:

Why do have to use a macro-variable? Are there datasets in the library whose name also begins with "rev"? If not you could use
data want;
  merge work.Rev:;
  by Id;
run;

Yes, in the case where the data sets of interest constitute all data sets whose name begins with Rev, this is much easier.

I am usually unwilling to make such an assumption about someone else's data, but certainly the original poster could know if such an assumption was warranted or not.