Solved
Contributor
Posts: 25

# Iterative summing

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!!!

Accepted Solutions
Solution
‎04-04-2014 02:52 AM
Contributor
Posts: 29

## Re: Iterative summing

Posted in reply to cindyforest7

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.

All Replies
Super User
Posts: 13,583

## Re: Iterative summing

Posted in reply to cindyforest7

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.

Solution
‎04-04-2014 02:52 AM
Contributor
Posts: 29

## Re: Iterative summing

Posted in reply to cindyforest7

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.

Contributor
Posts: 25

## Re: Iterative summing

THANK YOU!

🔒 This topic is solved and locked.

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

Discussion stats
• 3 replies
• 291 views
• 3 likes
• 3 in conversation