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?
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?
There is no such thing as a macro array. You can simulate arrays in macros in two ways:
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;
Use scan function
would you please give me the exact code using scan.thank you
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.
For use in macro language, there's the %scan() function.
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));
%put %sysnc(scan(&numVarList.,1));
exact case,this will work: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?
exact case,this will work:%put %sysnc(scan(&numVarList.,1));
proc sql;create table tableA asselect columnA into:valueA separated by ' ' from Map_numwhere variable='age';and this couldn't work,the error shows could not find column agecreate table tableA asselect columnA into:valueA separated by ' ' from Map_numwhere variable=%sysnc(scan(&numVarList.,1));why and how to fix it?
There is no such thing as a macro array. You can simulate arrays in macros in two ways:
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;
thank you.most of the answers are helpful,but yours is the best one with expanded knowledge and well explained.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.