DATA Step, Macro, Functions and more

SAS:how to use index to pick out macro array variable

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

SAS: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?


Accepted Solutions
Solution
‎03-02-2018 07:26 PM
PROC Star
Posts: 277

Re: SAS: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:

  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


All Replies
PROC Star
Posts: 1,405

Re: SAS:how to use index to pick out macro array variable

Use scan function

Contributor
Posts: 47

Re: SAS:how to use index to pick out macro array variable

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

Super User
Super User
Posts: 9,855

Re: SAS:how to use index to pick out macro array variable

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.

Super User
Posts: 10,592

Re: SAS:how to use index to pick out macro array variable

For use in macro language, there's the %scan() function.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 112

Re: SAS:how to use index to pick out macro array variable

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));
Contributor
Posts: 47

Re: SAS:how to use index to pick out macro array variable

Posted in reply to Satish_Parida
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?
Super User
Posts: 10,592

Re: SAS:how to use index to pick out macro array variable

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?

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-02-2018 07:26 PM
PROC Star
Posts: 277

Re: SAS: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:

  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;
Contributor
Posts: 47

Re: SAS:how to use index to pick out macro array variable

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

Contributor
Posts: 47

Re: SAS:how to use index to pick out macro array variable

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?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 299 views
  • 3 likes
  • 6 in conversation