Using a dynamic number in arrays

[TMorville]

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

[Patrick]

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

[TMorville]

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

[sbb]

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.

Suggested Google advanced search argument, this topic / post:

proc sql site:sas.com

[Ksharp]

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

[art297]

Toby,

I'm surprised that anyone could even attempt to answer your question without knowing what you incoming data look like and how you want the resulting data to appear.

Some example data, in the form of a datastep, would definitely help.

Art