topic Re: Do Until Loop with Condition on Running Total in SAS Programming

Do Until Loop with Condition on Running Total

topryde — Sun, 20 Jan 2019 01:02:59 GMT

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

Re: Do Until Loop with Condition on Running Total

Tom — Sun, 20 Jan 2019 01:10:26 GMT

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

Re: Do Until Loop with Condition on Running Total

topryde — Sun, 20 Jan 2019 01:17:02 GMT

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

Michelle

Re: Do Until Loop with Condition on Running Total

Tom — Sun, 20 Jan 2019 02:01:15 GMT

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

Re: Do Until Loop with Condition on Running Total

Astounding — Sun, 20 Jan 2019 02:36:08 GMT

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;

Re: Do Until Loop with Condition on Running Total

topryde — Sun, 20 Jan 2019 02:45:35 GMT

Thank you so much! This worked!!

Michelle

Re: Do Until Loop with Condition on Running Total

topryde — Sun, 20 Jan 2019 02:46:25 GMT

Hi Astounding,

Thanks so much! This has also worked!!

Michelle

Re: Do Until Loop with Condition on Running Total

Tom — Sun, 20 Jan 2019 02:52:44 GMT

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

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

Re: Do Until Loop with Condition on Running Total

topryde — Mon, 28 Jan 2019 08:50:47 GMT

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