Help using Base SAS procedures

Include dataset in SET statement only if it exists?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Include dataset in SET statement only if it exists?

[ Edited ]

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?


Accepted Solutions
Solution
‎08-30-2017 03:05 AM
Super User
Super User
Posts: 8,126

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

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;

View solution in original post


All Replies
Solution
‎08-30-2017 03:05 AM
Super User
Super User
Posts: 8,126

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

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;
Occasional Contributor
Posts: 8

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

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.

Super User
Posts: 10,280

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 8

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

Posted in reply to KurtBremser
Thanks, changed to uppercase and it worked.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 462 views
  • 2 likes
  • 3 in conversation