BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ltwo
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Tom
Super User Tom
Super User

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;
Ltwo
Fluorite | Level 6
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.

Kurt_Bremser
Super User

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;
Ltwo
Fluorite | Level 6
Thanks, changed to uppercase and it worked.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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