Iterative summing

[cindyforest7]

Hi,

This community is so awesome that I have learnt so many useful SAS tricks and techniques by asking questions and reading other's posts!

Now I have a table and I need to keep adding values of a variable from row to row and for each unique combination of keys.

Table have:

Member_name      provider_name      visit_date       visit_cost

aaa                       bbb                       1/1/2013         10

aaa                       bbb                       1/12/2013        40

aaa                       bbb                        2/23/2013        30

ccc                       bbb                        4/28/2012        100

ccc                       bbb                        5/1/2012           120

ccc                       bbb                         12/1/2012          50

ccc                       bbb                          1/3/2013          40

..............

Table want:   (I want to create a new variable called cost_incre)

Member_name      provider_name      visit_date       visit_cost          cost_incre

aaa                       bbb                       1/1/2013         10                  10

aaa                       bbb                       1/12/2013        40                  50

aaa                       bbb                        2/23/2013        30                 80

ccc                       bbb                        4/28/2012        100               100

ccc                       bbb                        5/1/2012           120               220

ccc                       bbb                         12/1/2012          50               270

ccc                       bbb                          1/3/2013          40               310

......

For a unique combination of member_name and provider_name, the cost_incre increases as visit_date increases.

Does any one know how to do this? I'm really bad at iterative steps.....do I need to create a counter for this?

Appreciated!!!

[ballardw]

Assuming the data is sorted by Member_name, provider_name and visit_date. If not sorted then do so before the next step.

data want;

     set have;

     Reatin cost_incr .;

     by Member_name provider_name;

     If first.provider_name then cost_incr = visit_cost;

     else cost_incr = cost_incr + visit_cost;

end;

might get you close.

[Chrishi · Accepted solution]

The above answer from ballardw is good but need small change instesd of first.provide_name  replace it with member_name.

Assuming the data is sorted by Member_name, provider_name and visit_date. If not sorted then do so before the next step.

data want;

set have;

retain cost_incr .;

by Member_name provider_name;

if first.member_name then cost_incr = visit_cost;

else cost_incr = cost_incr + visit_cost;

run;

Hope this solves you issue.

[cindyforest7]

THANK YOU!