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!!!
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.
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.
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.
THANK YOU!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.