topic Re: Creating an infinite number of variables in a loop in SAS Programming

Creating an infinite number of variables in a loop

Chiefsfan4ever — Tue, 18 Nov 2008 15:46:22 GMT

Here is my problem. I have a dataset and it has more than one bucket. Each row can have 5 buckets and it can essentially have infinite amount of rows.
Here is an example of the code I'm trying to write:

data work2;
set work1;
Extra=0;
retain extra;
NetCount = Maxcount-extra;
if ( NetCount >0 ) and (NetCount = count) then do;
Bucket||(1+5*(Netcount-1))=Bucket1;
Bucket||(2+5*(Netcount-1))=Bucket2;
Bucket||(3+5*(Netcount-1))=Bucket3;
Bucket||(4+5*(Netcount-1))=bucket4;
Bucket||(5+5*(Netcount-1))=bucket5;
end;
Extra = Extra+1;
run;

As you can see I'm trying to loop through each row and create buckets 1-5, buckets 6-10. I also have written code to find the Maximum amount of rows needed which is represented by Maxcount (Maxium of count). SAS won't let me create these new variables this way.

ANY IDEAS?

Thanks in advance.

Re: Creating an infinite number of variables in a loop

LinusH — Tue, 18 Nov 2008 20:35:02 GMT

It seem that you have borrowed some syntax from an other programming language (in your calculations for example). You said that you calculated Maxcount, how? If you pre-calculated, is it in every row of work1 table? A more convenient way is to store it in a macro variable.

Please attach some sample input data, and desired output, and maybe describe what a bucket is in this example.

Regards,
Linus

Re: Creating an infinite number of variables in a loop

Patrick — Wed, 19 Nov 2008 11:07:27 GMT

As Linus said: You will have to specify a bit more what you have and what you want to achieve in order to make us understand.

Just some ideas:
Have a look at the array statement, Proc Transpose, Proc Format and may be hash tables - Proc Format and Hash Tables would be used for table lookup.

Cheers, Patrick

Re: Creating an infinite number of variables in a loop

Chiefsfan4ever — Wed, 19 Nov 2008 15:17:08 GMT

Here is the code that gives me MaxCount:

data work;
set rec22;
retain polno1 count1;
if polno ~= polno1 then
count =1;
else if polno = polno1 then count=count1+1;
polno1= polno;
count1 =count;
run;

Proc Sort data=work;
by descending Count;
run;

Data work1;
set work;
retain Maxcount;
Maxcount = Max(Maxcount, count);
run; Message was edited by: Chiefsfan4ever

Re: Creating an infinite number of variables in a loop

Chiefsfan4ever — Wed, 19 Nov 2008 17:34:28 GMT

I'm not sure if I'm clarifying what my objective is here. Let me give an example:

Policy Bucket1 Bucket2 Bucket3 Bucket4 Bucket5 Count MaxCount
1 10 20 30 40 50 1 2
1 30 0 0 0 0 2 2
2 10 20 0 0 0 1 1

I would like the information to be displayed in the dataset this way:

Policy Bucket1 Bucket2 Bucket3 Bucket4 Bucket5 Bucket6
1 10 20 30 40 50 30
2 10 20 0 0 0 0

I don't know how many rows there will be so it could go out to Bucket100 or Bucket10000000 or what not. Hopefully this information will be helpful.

Re: Creating an infinite number of variables in a loop

LinusH — Wed, 19 Nov 2008 19:46:22 GMT

To avoid some heavy data step programming, I suggest that you first transpose the data downwards, so that you will have one roe per policy and bucket. Then you can us PROC TRANSPOSE to transpose your data upwards again. I think this is quite nice since you don't have to keep track of "anything", like maxcout etc:

data transpose1;
set policies;
array buckets[5] bucket1-bucket5;
do i = 1 to 5;
if buckets ne 0 then do;
bucket = buckets;
output;
end;
end;
keep policy bucket;
run;

proc transpose data=transpose1 out=transpose2(drop=_name_) prefix=bucket;
by policy;
var bucket;
run;

Hope this applies to your problem.

/Linus

Re: Creating an infinite number of variables in a loop

Chiefsfan4ever — Thu, 20 Nov 2008 17:10:18 GMT

Hey Linus,

I'm getting an error with this code. What am I missing?

335 if buckets ne 0 then do;
ERROR: Illegal reference to the array buckets.
336 bucket = buckets;
ERROR: Illegal reference to the array buckets.

Re: Creating an infinite number of variables in a loop

LinusH — Thu, 20 Nov 2008 17:23:48 GMT

It seems that the forum application didn't like my brackets in the code, sorry I didn't notice that. Let's try the same code with ordinary parenthesis:

data transpose1;
set policies;
array buckets(5) bucket1-bucket5;
do i = 1 to 5;
if buckets(i) ne 0 then do;
bucket = buckets(i);
output;
end;
end;
keep policy bucket;
run;

/Linus

Re: Creating an infinite number of variables in a loop

Chiefsfan4ever — Thu, 20 Nov 2008 18:58:40 GMT

Perfect Linus. Thank you so much. If you were here, I'd give you a hug.