Hi.....I am trying to calculate the accumulated differences between the cost and deductible amount for each ID. That is, I want to find the amount that exceeds the deductible for each ID as a new record is added for each ID. Thanks
ID | Date | Cost | Deductible | Amount |
11234 | 1/23/2016 | 175 | 250 | 0 |
11234 | 2/27/2016 | 50 | 250 | 0 |
11234 | 3/17/2016 | 75 | 250 | 50 |
11234 | 4/21/2016 | 45 | 250 | 95 |
11234 | 5/25/2016 | 90 | 250 | 185 |
12266 | 1/20/2016 | 150 | 300 | 0 |
12266 | 2/27/2016 | 75 | 300 | 0 |
12266 | 3/30/2016 | 50 | 300 | 0 |
12266 | 4/15/2016 | 45 | 300 | 20 |
1. Calculate a running total
2. Subtract total from deductible, set to 0 if applicable.
Data want;
Set have;
By id;
If first.id then running_total=cost;
Else running_total+cost;
Amount = ifn(running_total>deductible, running_total-deductible, 0);
Run;
1. Calculate a running total
2. Subtract total from deductible, set to 0 if applicable.
Data want;
Set have;
By id;
If first.id then running_total=cost;
Else running_total+cost;
Amount = ifn(running_total>deductible, running_total-deductible, 0);
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.