Merge data sets that some of them doesnt exist

[Ronein]

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*/

[PaigeMiller]

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.

[gamotte]

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.

[andreas_lds]

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;

[PaigeMiller]

---

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

[gamotte]

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)