topic Re: Macro arrays in SAS Procedures

Macro arrays

Raja — Thu, 06 Sep 2012 10:52:46 GMT

Hello All

I am trying to create an array with values taken from a data set. 'mydata' is a dataset with 100 observations stored under the column with name 'rate'. I want to store the 100 values into the variables alp1 to alp100 and use them for analysis.

%macro mymacro;

proc sql noprint;

select count(*) into :nob from mydata;

quit;

proc sql noprint;

select rate

into :alp1 - :alp&nob.

from mydata;

quit;

%do I = 1 %to &nob.;

%if (&&alp&I. > 0) %then

%do;

....

%end;

%mend mynmacro;

I amgetting an error at the step 'proc sql' .

Can anyone point out where I am going wrong?

Alternately can anyone suggest a correct approach using macro arrays?

Regards

Re: Macro arrays

shivas — Thu, 06 Sep 2012 11:13:49 GMT

Hi Raja,

Try this...

proc sql noprint;

select rate

into :alp1 - :alp%left(&nob.)

from mydata;

quit;

Thanks,

Shiva

Re: Macro arrays

Tom — Thu, 06 Sep 2012 11:20:48 GMT

You can simplify your code by using the SQLOBS automatic macro variable. This way you do not need two SQL statements or two passes through the data. When using SQL select into syntax to create a range of macro variables you do not need to set the upperbound exactly. You can just set it to some number large than you ever expect and SAS will only generate the number of variables that match the number of rows selected.

proc sql noprint;

select rate

into :alp1 - :alp9999

from mydata

;

%let nob=&sqlobs;

quit;

Re: Macro arrays

Patrick — Thu, 06 Sep 2012 11:44:36 GMT

If this doesn't need to be macro variables (you say "for analysis") then what about:

proc transpose data=mydata prefix=alp out=want(drop=_:);

var rate;

run;

Re: Macro arrays

DanielSantos — Thu, 06 Sep 2012 15:34:38 GMT

I would stick with shivas/Tom approach, but for the sake of diversity here's the same thing with datastep:

data _null_;

set mydata end=_END;

call symput(cats('alp',_N_),rate); * load value;

if _END then call symput('nob',_N_); * load count;

run;

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Macro arrays

Linlin — Thu, 06 Sep 2012 17:35:21 GMT

call symputx is better than call symput. look the difference in log file:

data rate;

input rate;

cards;

;

data _null_;

set rate end=_end;

call symput(cats('alp',_N_),rate); * load value;

call symputx(cats('nn',_N_),rate);

if _END then call symput('nob',_N_); * load count;

if _END then call symputx('n',_N_);

run;

%put _user_;

Re: Macro arrays

WaltSmith — Thu, 06 Sep 2012 19:13:06 GMT

The problem comes with the value stored in nob, it will be a string with the value "bbbbb100" (where b=blank).

Try this trick:

proc sql noprint;

select count(*) into :nob separated by ' ' from mydata;

quit;

Re: Macro arrays

DanielSantos — Fri, 07 Sep 2012 07:26:01 GMT

You're right Linlin, SYMPUTX is quite handy for removing leading/trailing blanks, but for this particular case, I really don't think it's necessary.

NOB/N is just a counter that will probably be used on some %do/%end cycle and I think it really won't make a difference having lead/trails blanks there. I'm guessing that only if you happen to load the value/affect into some alpha variable, then SYMPUTX will come handy. If it's numeric it won't make a difference either.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Macro arrays

WaltSmith — Fri, 07 Sep 2012 20:25:24 GMT

It makes the difference between actually working or failure - if &nob includes leading blanks (and it will using symput rather than symputx) then when &nob is used as attempted above, i.e.

proc sql noprint;

select rate

into :alp1 - :alp&nob.

from mydata;

quit;

then the "select" would resolve into: "select into :alp1 - alp 100 from mydata;" - and this will fail due to the leading blanks. What is needed in the above is "alp1 - alp100" with no leading blanks ahead of the numeric value. In a %do %end loop the string value of &nob won't matter (as long as its an integer).

Solutions above that will actually work are shivas, Tom, Linlin and my earlier suggestion (more than one way to write SAS code).

Re: Macro arrays

DanielSantos — Tue, 18 Sep 2012 07:50:39 GMT

Hi Walt.

I was referring to my own/Linlin's proposal of code (loading macros through datastep instead SQL select into), no difference at all.

As I said, NOB/N is to be used certainly in some %do/%end cycle, and it really doesn't matter there if there is trailing or leading blanks, see:

%macro test;

%let NOB= 10 ;

%do I=1 %to &NOB;

%put PREFIX&I;

%end;

%mend test;

%test;

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Macro arrays

WaltSmith — Thu, 20 Sep 2012 19:38:40 GMT

True that in a macro do loop, leading blanks in the value of NOB won't matter, but he wanted to use the value in:

proc sql noprint;

select rate

into :alp1 - :alp&nob.

from mydata;

quit;

where leading blanks will matter and cause an error as he said:

"I am getting an error at the step 'proc sql'"