turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Cumulative sum after conditional exclusion of some...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-04-2015 01:48 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kumarK

05-05-2015 04:18 AM

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**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to andreas_lds

05-05-2015 02:33 PM

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 .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kumarK

05-26-2015 10:50 AM

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;