topic Re: Create a macro variable consisting of a list SAS variable names, how to do it? in SAS Programming

Create a macro variable consisting of a list SAS variable names, how to do it?

Macro — Sat, 07 Sep 2013 03:10:41 GMT

Hi,

I know the list of SAS variables are: amount1, amount2, ..., amountj. How can I create a macro variable (called amounts) storing this list of variables separated by comma, so that I can use this list in future?

if j=20, then, something like amounts = amount1,amount2,...,amount20

Thanks.

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Jagadishkatam — Sat, 07 Sep 2013 07:09:44 GMT

check the below code

proc sql;

select name into: vname separated by ',' from dictionary.columns where libname='SASHELP' and memname='CLASS';

quit;

%put &vname;

Thanks,

Jagadish

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Macro — Sat, 07 Sep 2013 19:15:43 GMT

Jagadish, thanks for the answer. Indeed, I would like to avoid using dictionary.columns since this file is create as an intermediate file and is inside a loop. I don't want repeat this proc sql many times since I know the variable names already. I would like to create this name list before the loop and before the file is created.

So (1) I don't have the file at this stage, so I cannot use dictionary.columns.

(2) I would like to avoid using proc sql in the loop.

Therefore any other ways? Thanks.

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Tom — Sat, 07 Sep 2013 19:40:15 GMT

If you know the names then type them into the %LET statement.

If instead the idea is to create a list of names by incrementing a counter then use a %DO loop

%do i=1 %to &j;

%let varlist=&varlist amount&i ;

%end;

If you really want commas instead of spaces (but why would you want the commas?) in your list then you could add some extra logic to prevent an extra leading comma.

%let varlist=amount1;

%do i=2 %to &j;

%let varlist=&varlist,amount&i ;

%end;

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Astounding — Sat, 07 Sep 2013 19:40:58 GMT

You may not have to do anything at all. Consider this construct:

amount:

The colon at the end means that this represents a list of variables, all those variable names that begin with "amount". It can be used in both DATA and PROC steps. For example. a DATA step might use:

array amts {*} amount:;

do i=1 to dim(amts);

Good luck.

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Macro — Sat, 07 Sep 2013 21:12:26 GMT

Thanks Tom. In your first version with space as delimiter, the &varlist is not initialized, I tried the code and did not work. But second version worked. Does it mean that when using space as delimiter, then the first word in list does not need to be initialized. Can an empty word be used to initialize varlist so that the do loop can start from 1?

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Macro — Sat, 07 Sep 2013 21:15:04 GMT

Thanks Astounding. The data set file containing these names are not availabe at this stage, so I may not be able to use the way you suggested.

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

art297 — Sat, 07 Sep 2013 22:00:08 GMT

Then how about something like:

data _null_;

length vars $32767;

vars='amount1';

do i=2 to 20;

vars=catx(' ',vars,catt('amount',i));

end;

call symput('varlist',vars) ;

run;

%put &varlist.;

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Tom — Sun, 08 Sep 2013 03:56:27 GMT

When writing macros you should define the macro variables you create as local to prevent the use of them from accidentally changing the value of a macro variable that existed before the macro began execution. If you define a new macro variable with %LOCAL statement then it will be empty until you assign it a non empty value.

You can set a macro variable to empty using %LET. Example: %let varlist= ;

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Tom — Sun, 08 Sep 2013 04:10:04 GMT

How do you want to use the list?

If you want to define the variables in a data step then you really just need to know the upper bound, which you said was in the macro variable J.

data want ;

length amount1 - amount&j 8 ;

...

Re: Create a macro variable consisting of a list SAS variable names, how to do it?

Macro — Sun, 08 Sep 2013 05:56:14 GMT

I would like to use the name list in a summary function in a proc sql such as

select sum(0,&varlist) as amounts

That is the reason I like comma as seperator.