BookmarkSubscribeRSS Feed
Raja
Calcite | Level 5

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

10 REPLIES 10
shivas
Pyrite | Level 9

Hi Raja,

Try this...

proc sql noprint;

    select rate

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

    from mydata;

quit;

Thanks,

Shiva

Tom
Super User Tom
Super User

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
Opal | Level 21

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
Barite | Level 11

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
Lapis Lazuli | Level 10

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
Barite | Level 11

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
Fluorite | Level 6

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
Barite | Level 11

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

WaltSmith
Fluorite | Level 6

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
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1276 views
  • 0 likes
  • 7 in conversation