topic Re: Reference to a variable dataset name within a macro in SAS Programming

Reference to a variable dataset name within a macro

VCucu — Mon, 07 Sep 2020 13:31:21 GMT

i have a dataset "distincttenants" with following values: xxx yyy zzz I would like to be able to refer correctly in the macro "do" to the datasets prodxxx, prodyyy, prodzzz. How do I need to write the code to reference to the correct dataset ? obviously the text "prod" together (&) with the macro variable which i tried in the example below does not work. thank you

%macro do(tenant=); 
proc sql; 
create table a1 as select * from prod&tenant.; 
quit; 
%mend do; 

data distincttenants; 
set distincttenants; 
str=catt('%do(tenant=',tenantid,');'); 
call execute(str); 
run;

Re: Reference to a variable dataset name within a macro

Astounding — Mon, 07 Sep 2020 13:44:48 GMT

There are two things you need to do to get started.

First, change the name of the macro. %DO is a macro key word, and so would be illegal as the name of a macro.

Second, write a call to the macro that works for a single data set instead of trying to do all three data sets at once.

Once you have those pieces in place, you can re-post and get some help in how to call the macro three times.

Re: Reference to a variable dataset name within a macro

VCucu — Mon, 07 Sep 2020 13:50:35 GMT

1) thats fine, it was for illustration purpose; i have another one which is not "do" in the real application

2) i basically want to refer in a macro to a dataset name which is composed of two parts

- fixed text "prod"

- variable text coming from macro variable "xxx", "yyy", "zzz"

in the example provided using "prod&tenant." does not work

Re: Reference to a variable dataset name within a macro

VCucu — Mon, 07 Sep 2020 13:52:18 GMT

so i have prodxxx, prodyyy and prodzzz tables however SAS compiler within the macro does not understand i want to select from these datasets if i use "prod&tenant.";

Re: Reference to a variable dataset name within a macro

Astounding — Mon, 07 Sep 2020 13:56:30 GMT

There are many places where the logic could break down. That's why I asked for a simple macro call that processes one value. For example, what happens when you try to run:

%macroname_instead_of_do (tenant=xxx)

Re: Reference to a variable dataset name within a macro

PaigeMiller — Mon, 07 Sep 2020 14:19:08 GMT

I am going to second the request from @Astounding

That's why I asked for a simple macro call that processes one value.

We have to see your code, and as requested, we need to see code that works on one value. Words will not get us there.

Re: Reference to a variable dataset name within a macro

Kurt_Bremser — Mon, 07 Sep 2020 14:58:06 GMT

Since you always create the same dataset in the macro, only the result of the last macro call will "survive".

PS "does not work" on its own without further details is a very stupid and unhelpful statement.

Re: Reference to a variable dataset name within a macro

Tom — Mon, 07 Sep 2020 16:21:04 GMT

What part of it does not work? The posted code looks fine.

To test the code try it without any macro logic.

%let tenant=xxx;
proc contents data=prod&tenant ;
run;

Sometimes in macros the parser can get confused about what you want to treat as a single token. You can fix this with %unquote()

proc contents data=%unquote(prod&tenant) ;
run;

or simply assigning the generated name to a new macro variable and use that so there is no confusion about whether it is one token or two.

%let dsname=prod&tenant;
proc contents data=&dsname ;
run;

Re: Reference to a variable dataset name within a macro

VCucu — Mon, 07 Sep 2020 19:02:38 GMT

Hi Tom,

Thank you very much for the feedback. Your suggestion solved my issue.

%unquote(prod&tenant) ;

Best regards,