Help using Base SAS procedures

Macro arrays

Reply
New Contributor
Posts: 3

Macro arrays

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

Super Contributor
Posts: 349

Re: Macro arrays

Hi Raja,

Try this...

proc sql noprint;

    select rate

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

    from mydata;

quit;

Thanks,

Shiva

Super User
Super User
Posts: 7,039

Re: Macro arrays

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;

Respected Advisor
Posts: 4,173

Re: Macro arrays

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=_Smiley Happy;

  var rate;

run;

Super Contributor
Posts: 474

Re: Macro arrays

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

Super Contributor
Posts: 1,636

Re: Macro arrays

Posted in reply to DanielSantos

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

Super Contributor
Posts: 474

Re: Macro arrays

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

Contributor
Posts: 22

Re: Macro arrays

Posted in reply to DanielSantos

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

Super Contributor
Posts: 474

Re: Macro arrays

Posted in reply to WaltSmith

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. Smiley Wink

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

Contributor
Posts: 22

Re: Macro arrays

Posted in reply to DanielSantos

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


Contributor
Posts: 22

Re: Macro arrays

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;

Ask a Question
Discussion stats
  • 10 replies
  • 503 views
  • 0 likes
  • 7 in conversation