topic Re: Set Multiple Datasets Using Macors ?? in SAS Procedures

Set Multiple Datasets Using Macors ??

KevinC_ — Thu, 17 Oct 2013 19:04:48 GMT

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

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;

Re: Set Multiple Datasets Using Macors ??

Anotherdream — Thu, 17 Oct 2013 19:10:36 GMT

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

Re: Set Multiple Datasets Using Macors ??

Reeza — Thu, 17 Oct 2013 19:14:53 GMT

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;

Re: Set Multiple Datasets Using Macors ??

Anotherdream — Thu, 17 Oct 2013 19:20:47 GMT

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)

Re: Set Multiple Datasets Using Macors ??

KevinC_ — Thu, 17 Oct 2013 19:47:35 GMT

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

Re: Set Multiple Datasets Using Macors ??

Reeza — Thu, 17 Oct 2013 19:57:01 GMT

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;

Re: Set Multiple Datasets Using Macors ??

KevinC_ — Thu, 17 Oct 2013 20:18:38 GMT

Reeza,

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

Thank you again

Kevin