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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

10 REPLIES 10
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
Macro
Obsidian | Level 7

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.

Tom
Super User Tom
Super User

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;

Macro
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

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= ;

Astounding
PROC Star

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.

Macro
Obsidian | Level 7

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.

art297
Opal | Level 21

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.;

Tom
Super User Tom
Super User

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 ;

  ...

Macro
Obsidian | Level 7

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.

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!

How to Concatenate Values

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.

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
  • 10 replies
  • 1166 views
  • 3 likes
  • 5 in conversation