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.
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;
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
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.
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;
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?
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= ;
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.
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.
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.;
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 ;
...
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.