BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Geo-
Quartz | Level 8

I create a marco array using:

proc sql;
select distinct variable into:numVarList separated by ' ' from Map_num;

I used:

%put &numVarList{1};

and it gave me all variables:var1 var2 var3{1}

how to use index to pick out macro array variable?

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

There is no such thing as a macro array. You can simulate arrays in macros in two ways:

  1. As you did, put the stuff into a single string, with a delimiter (blank in your case).
  2. Generate a number of macro variables with the same prefix.

In case one, you can use the %SCAN macro function, (or, if you have quoted delimiters, %sysfunc(SCAN()) with the Q option) to access individual elements, in your case

 

%put %scan(&numVarlist,1,str( ));

You could also use the second solution:

 

 

proc SQL;
  select distinct variable into: numVar1-numVar999 from Map_num:

 

In which case you can get the first element like this:

 

%put &numVar1;

Or, if you are using macro variables to access the "array":

 

 

%let i=1;
%put &&numVar&i;

The double ampersand works like this: the macro interpreter scans the codes several times. Each time, double ampersands become single ampersands, and "unmatched" ampersands trigger interpretation of variables. So, after the first scan, &&numVar&i becomes &numVar1, in the second scan that becomes the value (var1 in your example).

 

When using the first method, you can use the COUNTW function to find the number of values, with the second option, it is not so obvious (you may look at which macro variables exist, but then, they may be variables generated in an earlier, similar SQL call). So in any case I advice you to store the value of the SQLobs macro variable after the SQL call:

proc SQL;
  select distinct variable into: numVar1-numVar999 from Map_num:
  %let nVars=&SQLobs;

View solution in original post

10 REPLIES 10
Geo-
Quartz | Level 8

would you please give me the exact code using scan.thank you

RW9
Diamond | Level 26 RW9
Diamond | Level 26

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000214639.htm

 

The question really is what are you intending to do with a list in a macro variable.  It is a question which repeatedly comes up on the forums, and all I ever see it doing is creating masses of messy macro code, which falls over half the time.  It really isn't a good way to be programming, and 99% of the time can be alleviated by simply re-thinking the problem.  Show what you use that macro for and I can explain further.

Satish_Parida
Lapis Lazuli | Level 10
proc sql;
select distinct variable into:numVarList separated by ' ' from Map_num;
quit;

%put &numVarList.;

%put %sysfunc(scan(&numVarList.,1));
%put %sysfunc(scan(&numVarList.,2));
%put %sysfunc(scan(&numVarList.,3));
%put %sysfunc(scan(&numVarList.,4));
%put %sysfunc(scan(&numVarList.,5));
%put %sysfunc(scan(&numVarList.,6));
Geo-
Quartz | Level 8
hi,using code below will not return char type value,how to fix?
%put %sysnc(scan(&numVarList.,1));
exact case,this will work:
proc sql;
create table tableA as
select columnA into:valueA separated by ' ' from Map_num
where variable='age';
 
and this couldn't work,the error shows could not find column age
create table tableA as
select columnA into:valueA separated by ' ' from Map_num
where variable=%sysnc(scan(&numVarList.,1));
 
why and how to fix it?
Kurt_Bremser
Super User

How should we validate this code without having the dataset map_num?

Please post example data in a data step, eg by using the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 


@Geo- wrote:
hi,using code below will not return char type value,how to fix?
%put %sysnc(scan(&numVarList.,1));
exact case,this will work:
proc sql;
create table tableA as
select columnA into:valueA separated by ' ' from Map_num
where variable='age';
 
and this couldn't work,the error shows could not find column age
create table tableA as
select columnA into:valueA separated by ' ' from Map_num
where variable=%sysnc(scan(&numVarList.,1));
 
why and how to fix it?

 

s_lassen
Meteorite | Level 14

There is no such thing as a macro array. You can simulate arrays in macros in two ways:

  1. As you did, put the stuff into a single string, with a delimiter (blank in your case).
  2. Generate a number of macro variables with the same prefix.

In case one, you can use the %SCAN macro function, (or, if you have quoted delimiters, %sysfunc(SCAN()) with the Q option) to access individual elements, in your case

 

%put %scan(&numVarlist,1,str( ));

You could also use the second solution:

 

 

proc SQL;
  select distinct variable into: numVar1-numVar999 from Map_num:

 

In which case you can get the first element like this:

 

%put &numVar1;

Or, if you are using macro variables to access the "array":

 

 

%let i=1;
%put &&numVar&i;

The double ampersand works like this: the macro interpreter scans the codes several times. Each time, double ampersands become single ampersands, and "unmatched" ampersands trigger interpretation of variables. So, after the first scan, &&numVar&i becomes &numVar1, in the second scan that becomes the value (var1 in your example).

 

When using the first method, you can use the COUNTW function to find the number of values, with the second option, it is not so obvious (you may look at which macro variables exist, but then, they may be variables generated in an earlier, similar SQL call). So in any case I advice you to store the value of the SQLobs macro variable after the SQL call:

proc SQL;
  select distinct variable into: numVar1-numVar999 from Map_num:
  %let nVars=&SQLobs;
Geo-
Quartz | Level 8

thank you.most of the answers are helpful,but yours is the best one with expanded knowledge and well explained.

Geo-
Quartz | Level 8

it is strange that

%put &numVarList.;

then I got:age agenc_non_ccbt_fnd_bal chmtpd_tmpnt_bal crnyr_cnter_tdnum

%put %sysnc(scan(&numVarList.,1,str( )));

I got:age agnc_non_ccb

why?and how to fix it?

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
  • 3602 views
  • 4 likes
  • 6 in conversation