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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Anotherdream
Quartz | Level 8

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

Reeza
Super User

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

Use Colon Operator (:)

data combine;

set perm.reason:;

run;

Anotherdream
Quartz | Level 8

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)

KevinC_
Fluorite | Level 6

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

Reeza
Super User

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;

KevinC_
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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