DATA Step, Macro, Functions and more

Creating an infinite number of variables in a loop

Reply
Occasional Contributor
Posts: 18

Creating an infinite number of variables in a loop

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.
Super User
Posts: 5,424

Re: Creating an infinite number of variables in a loop

Posted in reply to Chiefsfan4ever
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
Data never sleeps
Respected Advisor
Posts: 4,173

Re: Creating an infinite number of variables in a loop

Posted in reply to Chiefsfan4ever
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
Occasional Contributor
Posts: 18

Re: Creating an infinite number of variables in a loop

Posted in reply to Chiefsfan4ever
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
Occasional Contributor
Posts: 18

Re: Creating an infinite number of variables in a loop

Posted in reply to Chiefsfan4ever
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.
Super User
Posts: 5,424

Re: Creating an infinite number of variables in a loop

Posted in reply to Chiefsfan4ever
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
Data never sleeps
Occasional Contributor
Posts: 18

Re: Creating an infinite number of variables in a loop

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.
Super User
Posts: 5,424

Re: Creating an infinite number of variables in a loop

Posted in reply to Chiefsfan4ever
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
Data never sleeps
Occasional Contributor
Posts: 18

Re: Creating an infinite number of variables in a loop

Perfect Linus. Thank you so much. If you were here, I'd give you a hug.
Ask a Question
Discussion stats
  • 8 replies
  • 220 views
  • 0 likes
  • 3 in conversation