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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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