topic Re: Using a dynamic number in arrays in SAS Procedures

Using a dynamic number in arrays

TMorville — Tue, 28 Dec 2010 10:00:54 GMT

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

Re: Using a dynamic number in arrays

Patrick — Tue, 28 Dec 2010 11:28:15 GMT

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

Re: Using a dynamic number in arrays

TMorville — Tue, 28 Dec 2010 12:24:05 GMT

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

Re: Using a dynamic number in arrays

sbb — Tue, 28 Dec 2010 12:56:03 GMT

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

Re: Using a dynamic number in arrays

Ksharp — Wed, 29 Dec 2010 01:28:38 GMT

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

Re: Using a dynamic number in arrays

art297 — Fri, 31 Dec 2010 02:16:35 GMT

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