Hi,
I have the dataset called Have 3 (provided below) where i wanted to do a running total by SID until the running total reaches 1, of which then it should restart the calculation. The ending result i wanted to achieve is like table below: For example for SID 11, running total of row 1 and 2 is 1, so from row 3 the calculation should restart until it get to row 4 where the running total is 1 again.
Row | SID | amt | running_total(Until amt=1) |
1 | 11 | 0.5 | 0.5 |
2 | 11 | 0.5 | 1 |
3 | 11 | 0.5 | 0.5 |
4 | 11 | 0.5 | 1 |
5 | 11 | 0.25 | 0.25 |
6 | 11 | 0.25 | 0.5 |
7 | 22 | 0.5 | 0.5 |
8 | 22 | 0.5 | 1 |
9 | 22 | 0.25 | 0.25 |
10 | 22 | 0.25 | 0.5 |
11 | 22 | 0.25 | 0.75 |
12 | 22 | 0.25 | 1 |
13 | 44 | 0.5 | 0.5 |
14 | 44 | 0.5 | 1 |
15 | 44 | 0.25 | 0.25 |
I tried something like below, but it came out so wrong!!
Data test;
Do Until (running_total =1);
Set HAVE3;
By SID;
If first.sid then running_total =0;
Running_total + AMT;
End;
Run;
I am wondering if someone could give me a hand on this?? Many many thanks!
Below is the dataset: have3 i've used.
data have3;
infile cards expandtabs truncover;
input Row SID $ amt $ ;
cards;
1 11 0.5
2 11 0.5
3 11 0.5
4 11 0.5
5 11 0.25
6 11 0.25
7 22 0.5
8 22 0.5
9 22 0.25
10 22 0.25
11 22 0.25
12 22 0.25
13 44 0.5
14 44 0.5
15 44 0.25
;
RUN;
Thankyou
Michelle
Is this what you want?
data want ;
do until (last.sid or total >= 1);
set have3;
by sid;
total = sum(total,amt);
end;
run;
Obs total row sid amt 1 1.00 2 11 0.50 2 1.00 4 11 0.50 3 0.50 6 11 0.25 4 1.00 8 22 0.50 5 1.00 12 22 0.25 6 1.00 14 44 0.50 7 0.25 15 44 0.25
Is this what you want?
data want ;
do until (last.sid or total >= 1);
set have3;
by sid;
total = sum(total,amt);
end;
run;
Obs total row sid amt 1 1.00 2 11 0.50 2 1.00 4 11 0.50 3 0.50 6 11 0.25 4 1.00 8 22 0.50 5 1.00 12 22 0.25 6 1.00 14 44 0.50 7 0.25 15 44 0.25
Thank you Tom. Is it possible to still have the running total showing for each of the 15 rows in the output table?
Michelle
Thank you so much! This worked!!
Michelle
Hi Astounding,
Thanks so much! This has also worked!!
Michelle
What do you want to happen when running total is GREATER than 1?
For example: 0.5, 0.7, 0.2
Do you want to output to be:
0.5 0.5 0.7 1.2 0.2 0.2
or
0.5 0.5 0.7 0.7 0.2 0.9
Or perhaps split the value into two observations?
0.5 0.5 0.5 1.0 0.2 0.2 0.2 0.4
Sorry, if this is response is not too late yet...
if the running total is greater than 1, i think i'd prefer option 1.
0.5 0.5 0.7 1.2 0.2 0.2
Michelle
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.