sum up rows and divide till it reach a specific value

[newbie69]

Hi! 

I have the following problem, I would like to sum up a column and divide the sum every line through the sum of the whole column till a specific value is reached. so in Pseudocode it would look like that:

data;

sum_of_whole_column = sum(column);

subtotal[i] = 0;

i =1;

do until (subtotal[i] = 70000)

subtotal[i] = (subtotal[i] + subtotal[i+1])/sum_of_whole_column

i = i+1

end;

but I don't know if the Syntax is right, can I use that subtotal[i] to get the value in a specific cell? so I would like to run through an existing column... 

Many thanks in advance!

Best regards 

Edit: I get the error that I haven't defined an array... so can I use something else instead of subtotal[i]?

[PaigeMiller]

Syntax is not right, I can fix that. You did not define an array, and you don't need an array here. Arrays let you do things across different columns, which you don't seem to have here.

Your logic may not be right, I have no idea what you want. When you divide a value in a column by the sum of a column, the result cannot be 70000. The result is a percent (I guess if you have HUGE positive and negative numbers, maybe you can get 70000, but otherwise not)

Try this:

proc summary data=have;
    var column;
    output out=sum sum=sum_of_whole_column;
run;
data want;
    if _n_=1 then set sum;
    set have;
    subtotal + column; /* Cumulative sum */
    cumulative_percent=subtotal / sum_of_whole_column;
run;

From there, you can delete whatever you want until you get the desired cumulative percent.

[newbie69]

hey, first of all thank you very much, that's exactly what I need! The only thing that I also want is that it stop if I reach a specific percentage, that's the reason why I used that "do until", so I want to stop the process when the percentage is for example 70%. thanks and best regards

[PaigeMiller · Accepted solution]

you can modify DATA WANT as follows:

data want;
    if _n_=1 then set sum;
    set have;
    subtotal + column; /* Cumulative sum */
    cumulative_percent=subtotal / sum_of_whole_column;
    if cumulative_percent > 0.7 then stop;
run;

As noted before, I don't know what will happen if there are both negative and positive numbers in variable COLUMN, then the logic above probably needs to be modified (or maybe its fine the way it is).