Macro arrays

[Raja]

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;

%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

[shivas]

Hi Raja,

Try this...

proc sql noprint;

    select rate

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

    from mydata;

quit;

Thanks,

Shiva

[Tom]

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;

[Patrick]

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;

[DanielSantos]

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

[Linlin]

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

data rate;

input rate;

cards;

2

3

;

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

[DanielSantos]

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

[WaltSmith]

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

[DanielSantos]

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

[WaltSmith]

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'"

[WaltSmith]

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;