Not applicable
Posts: 0

# creating dataset

Hi,

i have data set like :
data dpd;
input bucket;
datalines;
1
2
5
6
7
8
9
3
4
7
;

if sum of that values are >6 then rank value=1 and it will be incremented.
i want to create a dataset like below:::

bucket rank
1 1
2 1
5 1
6 2
7 2
8 3
9 4
3 5
4 5
7 6
Not applicable
Posts: 0

## Re: creating dataset

Sorry I don't understand what you are looking for, I wrote the code below but this does not result in what you have. Can you explain a bit more. Do you mean the sum of all the previous values, all the values since the rank was incremented, the last two values?

data ranked;
retain rank 1 sum_vals;
set dpd;
sum_vals+bucket;
if sum_vals>6 then do;
rank+1;
sum_vals=0;
end;
run;
Not applicable
Posts: 0

## Re: creating dataset

This is My dataset
data dpd;
input bucket;
datalines;
1
2
5
6
7
8
9
3
4
7
;

The output:
Bucket rank
1 1
2 1
5 1
6 2
7 2
8 3
9 4
3 5
4 5
7 6

in the above output
first => add the values up to > 6 : 1+2+5 >6
rank value is 1 for 3 observations =1

second=> add the values up to >6 : 6+7 >6
rank value is 2 for 2 observations =2

third => add the values up to > 6 : 8 >6
rank value is 3 for 1 observations =3

fourth=> add the values up to > 6: 9 >6
rank value is 4 for 1 observations =4

fith=> add the values up to > 6: 3+4 >6
rank value is 5 for 2 observations =5

sixth=> add the values up to > 6 7 >6
rank value is 6 for 1 observations =6
Frequent Learner
Posts: 1

## Re: creating dataset

You should be able to do this with retain statement if I understand it correctly.
Just make sure that you do the output before increasing the rank count.
eg:

data test(drop=total);
set dpd;
retain total 0 group 1;
total=total+bucket;
output;
if total>6 then do;
group=group+1;
total=0;
end;
run;
Not applicable
Posts: 0

## Re: creating dataset

Thanks for clearing that up. My code would almost have done it, just shift the sum statement below the logic which checks it's value.

data ranked;
retain rank 1 sum_vals;
set dpd;
if sum_vals>6 then do;
rank+1;
sum_vals=0;
end;
sum_vals+bucket;
run;
Discussion stats
• 4 replies
• 151 views
• 0 likes
• 2 in conversation