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 |
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;
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 .
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.