Do Until Loop with Condition on Running Total

[topryde]

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

[Tom · Accepted solution]

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

[topryde]

Thank you Tom. Is it possible to still have the running total showing for each of the 15 rows in the output table? 

Michelle 

[Tom]

If you want to output all of the observations and not just the totals then add an OUTPUT statement inside the DO loop.

[topryde]

Thank you so much! This worked!!

Michelle

[Astounding]

Perhaps a simple program?

Data want;
Set have;
By sid;
Running_total + amt;
If first.sid or running_total > 1 then
Running_total = amt;
Run;

[topryde]

Hi Astounding,

Thanks so much! This has also worked!!

Michelle

[Tom]

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

[topryde]

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