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

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

Geo- — Fri, 02 Mar 2018 09:24:50 GMT

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?

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

PeterClemmensen — Fri, 02 Mar 2018 09:27:37 GMT

Use scan function

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

Geo- — Fri, 02 Mar 2018 09:41:04 GMT

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

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

RW9 — Fri, 02 Mar 2018 09:54:46 GMT

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.

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

Kurt_Bremser — Fri, 02 Mar 2018 09:59:42 GMT

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

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

Satish_Parida — Fri, 02 Mar 2018 09:59:47 GMT

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

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

s_lassen — Fri, 02 Mar 2018 11:12:52 GMT

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

As you did, put the stuff into a single string, with a delimiter (blank in your case).
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;

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

Geo- — Sat, 03 Mar 2018 00:26:41 GMT

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

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

Geo- — Mon, 05 Mar 2018 01:47:33 GMT

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?

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

Geo- — Mon, 05 Mar 2018 02:01:28 GMT

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?

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

Kurt_Bremser — Mon, 05 Mar 2018 06:37:06 GMT

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-data-AKA-generate/ta-p/258712

@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?