Contributor
Posts: 43

# Using a dynamic number in arrays

Hi everyone and merry xmas!

I have a quick question, that i don't quite know how to google.

I have a datastep with arrays in it:

data rotate;
set retain_test;
by bp_2;
retain numabb17-numabb43;
array Anumabb(17:43) numabb17-numabb43;
if first.bp_2 then do;
do i = 17 to 43;
Anumabb = .;
end;
end;
Anumabb(week) = numabb;
if last.bp_2 then output;
drop week i;
run;

This is just part of the code. What i would like to do, is to have two variables for the weeknumbers. So instead of writeing 17 and 43, i predefine them above, and then i can change them as i wish to.

Im thinking something like this:

startweek = 17
endweek = 43

data rotate;
set retain_test;
by bp_2;
retain numabb(startweek)-numabb(endweek);
array Anumabb((startweek)endweek) numabb(startweek)-numabb(endweek);
if first.bp_2 then do;
do i = (startweek) to (endweek);
Anumabb = .;
end;
end;
Anumabb(week) = numabb;
if last.bp_2 then output;
drop week i;
run;

Any ideas?

Thanks!
Toby Message was edited by: TMorville
Posts: 4,736

## Re: Using a dynamic number in arrays

Hi

Below code probabely does what you're after:

proc sql noprint;
select cats(max(week)), cats(min(week)) into :maxweek, :minweek
from retain_test
;
quit;

/* alternative to SQL above - but less dynamic:
%let minweek=17;
%let maxweek=43;
*/

data rotate;
set retain_test;
by bp_2;
array Anumabb(*) 8 numabb&minweek. - numabb&maxweek.;
retain numabb&minweek. - numabb&maxweek.;

if first.bp_2 then call missing(of Anumabb(*));

Anumabb(week) = numabb;

if last.bp_2 then output;

drop week;
run;

I assume in your real code there are more transformations.
If not then Proc Transpose might be another way to go.

proc transpose data=retain_test out=rotate prefix=numabb;
by bp_2;
id week;
var numabb;
run;

HTH
Patrick

Message was edited by: Patrick added missing SELECT to SQL

Message was edited by: Patrick
Contributor
Posts: 43

## Re: Using a dynamic number in arrays

Hi Patrick, and thanks for the reply.

I can't get the sql to work, it says that the "cats" argument is unknown or used out of order.

Im not very experienced with sql, so i have no idea where to start troubleshooting.

-t
Super Contributor
Posts: 3,176

## Re: Using a dynamic number in arrays

The CATS (and other CAT-related functions were added with SAS 9), however I also notice that the PROC SQL code is missing a "SELECT".

Scott Barry
SBBWorks, Inc.

proc sql site:sas.com
Super User
Posts: 10,778

## Re: Using a dynamic number in arrays

Hi.
You can remove cats(), directly use max(week).
I think Patrick used function cats() is just to want to delete the surplus blanks.
Actually ,the macro variable will automatically remove the heading and trailing blanks.

Sorry. I am wrong. Actually Proc Sql will keep heading and trailing blanks for the single macro variable.

Ksharp Message was edited by: Ksharp
PROC Star
Posts: 8,164