topic Calling Macro Variable to be used as part of Array Statement in SAS Procedures

Calling Macro Variable to be used as part of Array Statement

AvocadoRivalry — Fri, 30 Sep 2011 20:57:47 GMT

I have the following arrays I need defined, which worked fine with the original code:

array T Ts1-Ts50;

array Q Qs1-Qs50;

Due to some code changes, there will not always be 50 values and the number of observations is being defined as a macro variable earlier in the program. I'm not sure if this is something specific to arrays or I'm just doing something silly when trying to call the macro variable in the name for the arrays, but hopefully this is a quick fix. Here is the full set of code that tries to reference the macro variable to replace the Ts50 or Qs50 with the number needed:

data Default;
set Default;
if _N_ eq 1 then do; set Ts; set Quants; end;

array T Ts1-Ts&bins_needed.;
array Q Qs1-Qs&bins_needed.;

if quick_ratio lt Qs1 then T_QUICK = Ts1;
else if quick_ratio ge Qs&bins_needed. then T_QUICK = Ts&bins_needed.;
else do;
do i = 1 to (&bins_needed. - 1);
if quick_ratio ge Q and quick_ratio lt Q[i+1] then T_QUICK = T;
end;
end;

%end;

drop Ts1-Ts&bins_needed. Qs1-Qs&bins_needed. i;
run;

Calling Macro Variable to be used as part of Array Statement

art297 — Fri, 30 Sep 2011 22:26:30 GMT

You didn't provide any example data, or desired result based on that data, thus one can only guess. Hopefully, the following is at least close to what you want to accomplish:

*Build some test data;

data ts (drop=i);

array ts(3);

do i=1 to 3;

ts(i)=i;

end;

run;

data Quants (drop=i);

array qs(3);

do i=1 to 3;

qs(i)=i;

end;

run;

data Default;

input quick_ratio;

cards;

;

data Default;

set Default;

if _N_ eq 1 then do;

set Ts;

set Quants;

array _T(*) Ts:;

array _Q(*) Qs:;

retain _T: _Q:;

end;

if quick_ratio lt _Q(1) then T_QUICK = _T(1);

else if quick_ratio ge _Q(dim(_Q)) then T_QUICK = _T(dim(_Q));

else do;

do i = 1 to dim(_Q)-1;

if quick_ratio ge _Q and quick_ratio lt _Q[i+1]

then T_QUICK = _T;

end;

drop ts: Qs: i;

run;

Calling Macro Variable to be used as part of Array Statement

FriedEgg — Fri, 30 Sep 2011 22:34:06 GMT

I'm not sure I understand what you are asking. I do some minor cleanup and remove an erronious %end;

Are you wanting to determine the value of bins_needed or explicitly define it, like I have here?

The proper syntax for an array statement is available here: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201956.htm

%let bins_needed=15;

data default;

set default;

if _N_ eq 1 then

do;

set Ts;

set Quants;

end;

array T

Ts1-Ts&bins_needed;

array Q

Qs1-Qs&bins_needed;

if quick_ratio lt qs[1] then

T_QUICK = ts[1];

else if quick_ratio ge Qs&bins_needed then

T_QUICK = ts[&bins_needed];

else

do i = 1 to (&bins_needed - 1);

if quick_ratio ge Q and quick_ratio lt Q[i+1] then T_QUICK = T;

end;

drop Ts1-Ts&bins_needed Qs1-Qs&bins_needed i;

run;

Calling Macro Variable to be used as part of Array Statement

Tom — Fri, 30 Sep 2011 23:32:57 GMT

I am sorry, but what is the question? Did you get an error message? Or are you not getting the expected results?

Re: Calling Macro Variable to be used as part of Array Statement

Tom — Sat, 01 Oct 2011 15:21:42 GMT

Looks like you are just trying to generate T_QUICK based on which bin QUICK_RATIO falls into.

Does not look like you even need to know how many bins there are to do that.

Take a look at this example. Play with the condition in the UNTIL() clause to get it to pick the right bin.

data v_quants;

input ratio @@;

cards;

0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0

run;

proc transpose out=quants(drop=_:) prefix=Qs;

run;

data Default;

input quick_ratio @@;

if _N_ eq 1 then set Quants;

array qs qs: ;

do i=1 to dim(qs) until (quick_ratio < qs(i) );

t_quick = qs(i);

end;

drop qs: i ;

cards;

0.05 0.15 0.2 1.0 1.5

run;

proc print;

run;

quick_

Obs ratio t_quick

1 0.05 0.1

2 0.15 0.2

3 0.20 0.3

4 1.00 1.0

5 1.50 1.0

Re: Calling Macro Variable to be used as part of Array Statement

shinnen1 — Mon, 03 Oct 2011 15:00:00 GMT

If you want to dynamically set the number of items in an array you can call a macro variable as follows in the array statement.

%let periods = 99;

data xxxx.....

array personnel_expense {*} personnel_expense1 - personnel_expense%eval(&periods);

run;

Calling Macro Variable to be used as part of Array Statement

AvocadoRivalry — Mon, 03 Oct 2011 15:20:38 GMT

Thanks Shinnen1 - that was exactly what I was looking for. I wanted to change the original code as little as possible, as it was not written by me, so the %eval function is exactly what I needed. The problem I was having was creating a flexible value to define the last element in the array each time the macro runs and that syntax seems to work exactly as I need it to.

Calling Macro Variable to be used as part of Array Statement

Tom — Mon, 03 Oct 2011 15:48:12 GMT

If that fixes your problem then most likely the underlying cause is that the macro variable has spaces in it.

I usually use this simple code to fix that.

%let bins_needed=&bins_needed;

I find this usually happens when using PROC SQL to generate a count.

proc sql noprint ;

select count(*) into :count from sashelp.class;

quit;

%put count="&count";

%let count=&count;

%put count="&count";

395 %put count="&count";

count=" 19"

396 %let count=&count;

397 %put count="&count";

count="19"

Calling Macro Variable to be used as part of Array Statement

AvocadoRivalry — Mon, 03 Oct 2011 15:58:21 GMT

That could have been it. I did use PROC SQL to generate the bins_needed variable.