topic Re: Include dataset in SET statement only if it exists? in SAS Procedures

Include dataset in SET statement only if it exists?

Ltwo — Wed, 30 Aug 2017 04:33:10 GMT

Hi, I'm looking to code up a data step at the end of my program that concatenates all existing datasets. I currently have to do it manually by checking which datasets exist. See example below.

data final;
set
group_1-group_10
team_1-team_10;
run;

Sometimes one or more of the datasets might not exist (and that's fine) but is there a quicker way to code this so I do not have to manually check which datasets exists and commenting out before running the data step?

Re: Include dataset in SET statement only if it exists?

Tom — Wed, 30 Aug 2017 05:18:17 GMT

You could just query the metadata to see what membernames match your patterns. For example this query will just look for any members that start with GROUP_ or TEAM_ but you could make it more complicated if you required that the part after the underscore was an integer. Or even an integer between 1 and 10.

Note that if you preset the macro variable to _NULL_ before the SQL query then when the data step runs it will generate and empty (zero obs and zero vars) dataset when no members match your pattern.

proc sql noprint ;
%let memlist=_null_;
  select catx('.',libname,memname)
    into :memlist separated by ' '
    from dictionary.members 
    where libname='WORK'
      and ((memname like 'GROUP^_%' escape '^')
        or (memname like 'TEAM^_%' escape '^'))
  ;
quit;

data want ;
  set &memlist;
run;

Re: Include dataset in SET statement only if it exists?

Ltwo — Wed, 30 Aug 2017 05:40:55 GMT

data WORK.group_1;
a = 123;
run;

proc sql noprint ;
%let memlist=_null_;
  select catx('.',libname,memname)
    into :memlist separated by ' '
    from dictionary.members 
    where libname='WORK'
      and ((memname like 'group^_%' escape '^'))
  ;
quit;

I tried this but I'm getting this.

NOTE: No rows were selected.

Re: Include dataset in SET statement only if it exists?

Kurt_Bremser — Wed, 30 Aug 2017 06:07:46 GMT

When selecting for library or dataset names, always use uppercase:

data WORK.group_1;
a = 123;
run;

proc sql noprint ;
%let memlist=_null_;
  select catx('.',libname,memname)
    into :memlist separated by ' '
    from dictionary.members 
    where libname='WORK'
      and ((memname like 'GROUP^_%' escape '^'))
  ;
quit;

%put &memlist;

Re: Include dataset in SET statement only if it exists?

Ltwo — Wed, 30 Aug 2017 06:21:36 GMT

Thanks, changed to uppercase and it worked.