DATA Step, Macro, Functions and more

re: Accumulated Difference

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

re: Accumulated Difference

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

Accepted Solutions
Solution
‎06-02-2016 10:07 AM
Super User
Posts: 17,812

Re: re: Accumulated Difference

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;

View solution in original post


All Replies
Solution
‎06-02-2016 10:07 AM
Super User
Posts: 17,812

Re: re: Accumulated Difference

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 165 views
  • 0 likes
  • 2 in conversation