Help using Base SAS procedures

Set Multiple Datasets Using Macors ??

Accepted Solution Solved
Reply
Regular Contributor
Posts: 173
Accepted Solution

Set Multiple Datasets Using Macors ??

Hello Everyone,

I have a data step that reads multiple datasets.  I run this report once every month and the input datasets change every month.  I am trying to automate this job so there will be no manual updates to the code.  The code below utilizes dictionary.member to select all the datasets starting with the name 'REASON' in the libname 'PERM'.  Then I select these dataset names into a macro variable.  The code works fine.  The problem is I can't utilize the macro variable in the set statement for my data step (where you see the '???').

Is there a way to fix my approach?  Are there are other ways to accomplish this task?  

I really appreciate any input you may have Smiley Happy

proc sql;

create table reasons as

select distinct memname

from

dictionary.members

where upcase(libname) = 'PERM'

and upcase(memname) like 'REASON%';

quit;


proc sql;

select distinct quote(memname) into :mem_list

seperated by ', '

from reasons;

quit;

data combine;

set ????

run;


Accepted Solutions
Solution
‎10-17-2013 03:57 PM
Super User
Posts: 17,868

Re: Set Multiple Datasets Using Macors ??

I think the colon is introduced for datasets from 9.2 +

Anyways try this (untested):

proc sql;

select catx(".", libname, memname) into :mem_list     Separated by ' '

from reasons;

quit;

data combine;

set &mem_list;

run;

View solution in original post


All Replies
Super Contributor
Posts: 418

Re: Set Multiple Datasets Using Macors ??

your macro creation is incorrect.

You are sepearting them by a comma, but the sas set statement doesn't allow commas. SO just change it to separated by a space. aka change your code too.

proc sql;

select distinct quote(memname) into :mem_list     Separated by ' '

from reasons;

quit;

data combine;

set &mem_list;

run;

This will work

Super User
Posts: 17,868

Re: Set Multiple Datasets Using Macors ??

I think it would also need to concatenate the libname and memname somehow instead of just the memname.

Use Colon Operator (Smiley Happy

data combine;

set perm.reason:;

run;

Super Contributor
Posts: 418

Re: Set Multiple Datasets Using Macors ??

In case your wondering, Reeza's code does the exact same thing without the fuss of using the macro processing.

So I would use his for efficiency sake plus simplicity of code! I was attempting to solve the problem in your method however, so sorry for not pointing that out. (I recently learned about the colon operator myself)

Regular Contributor
Posts: 173

Re: Set Multiple Datasets Using Macors ??

Thank you so much Reeza and Anotherdream for your input !

I modified my code using the colon operator:

data combine;

set perm.reason:;

run;

It generated these errors:

"ERROR: File PERM.REASON.DATA does not exist.  Syntax error, expecting one of the following: a name, a quoted string,..."

"ERROR 200-322: The symbol is not reconized and will be ignored."

After seeing the errors I did a little test:  I changed the set statement to "set perm.reason_2013_09" and it worked just fine.  So this tells me the file(s) are there.  It seems to have problems with the colon?

Any suggestions?  Thanks again.

Btw, i use SAS 9.1.3

Solution
‎10-17-2013 03:57 PM
Super User
Posts: 17,868

Re: Set Multiple Datasets Using Macors ??

I think the colon is introduced for datasets from 9.2 +

Anyways try this (untested):

proc sql;

select catx(".", libname, memname) into :mem_list     Separated by ' '

from reasons;

quit;

data combine;

set &mem_list;

run;

Regular Contributor
Posts: 173

Re: Set Multiple Datasets Using Macors ??

Reeza,

Your code worked!!  The colon operator seems pretty slick.  Too bad our SAS version doesn't support it.

Thank you again Smiley Happy

Kevin

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 237 views
  • 0 likes
  • 3 in conversation