BookmarkSubscribeRSS Feed
Chiefsfan4ever
Calcite | Level 5
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.
8 REPLIES 8
LinusH
Tourmaline | Level 20
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
Patrick
Opal | Level 21
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
Chiefsfan4ever
Calcite | Level 5
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
Chiefsfan4ever
Calcite | Level 5
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.
LinusH
Tourmaline | Level 20
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
Chiefsfan4ever
Calcite | Level 5
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.
LinusH
Tourmaline | Level 20
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
Chiefsfan4ever
Calcite | Level 5
Perfect Linus. Thank you so much. If you were here, I'd give you a hug.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1294 views
  • 0 likes
  • 3 in conversation