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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: