topic sum up rows and divide till it reach a specific value in SAS Programming

sum up rows and divide till it reach a specific value

newbie69 — Sun, 06 Mar 2022 17:53:36 GMT

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]?

Re: sum up rows and divide till it reach a specific value

PaigeMiller — Sun, 06 Mar 2022 19:17:47 GMT

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.

Re: sum up rows and divide till it reach a specific value

newbie69 — Sun, 06 Mar 2022 19:26:25 GMT

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

Re: sum up rows and divide till it reach a specific value

PaigeMiller — Sun, 06 Mar 2022 20:18:42 GMT

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).