topic Re: Cumulative sum after conditional exclusion of some rows in SAS Programming

Cumulative sum after conditional exclusion of some rows

kumarK — Mon, 04 May 2015 17:48:19 GMT

Hi All,

I have this dataset ordered by ID. I want cumulative sum with respective of grand total. if the cumulative sum crosses the grand total then not considering those records and flag those records as "Crosed Gtotal" or any other, then the cumulative sum goes to the the next records.

Here Gtotal is: 60ding some rows

data have;

input id Count;

cards;

101 15

102 35

103 30

104 25

105 50

106 4

107 25

108 5

run;

Data want:

Branch	Count	Total
101	15	15
102	35	50
103	30	crosses GTotal
104	25	crosses GTotal
105	50	crosses GTotal
106	4	54
107	25	crosses GTotal
108	5	59

Re: Cumulative sum after conditional exclusion of some rows

andreas_lds — Tue, 05 May 2015 08:18:54 GMT

Not sure if i fully understood your requirement.

%let GrandTotal = 60;

data work.want;

set work.have;

length Total 8;

retain Total 0;

Total = Total + Count;

output;

if Total >= &GrandTotal then do;

Total = Total - Count;

end;

run;

proc format;

value GTotal

low -< &GrandTotal = [best32.]

&GrandTotal - high = 'crosses GTotal'

;

run;

proc print data=work.want;

format Total GTotal.;

run;

Re: Cumulative sum after conditional exclusion of some rows

kumarK — Tue, 05 May 2015 18:33:45 GMT

Thanks Andreas,

This is what i am looking. Thanks for your time.

Can we find out the crossed GTotatal id's row numbers? ie. for 103 -row num is 3 , for 104 row num is 4 .

Re: Cumulative sum after conditional exclusion of some rows

andreas_lds — Tue, 26 May 2015 14:50:58 GMT

Sorry for the delayed response, summer holiday started earlier than normal this year 😉

The automatic-variable "_n_" contains the observation-number - as long as you don't use a where statement in the data step.

data work.want;

set work.have;

length Total RowNumber 8;
retain Total 0;

Total = Total + Count;

if Total >= &GrandTotal then do;
RowNumber = _n_;
end;

output;

if Total >= &GrandTotal then do;
Total = Total - Count;
end;
run;