Hi Everybody,
I need help separating two variables on a monthly basis.
Specifically, I have data on patients admitted to hospitals. The four variables are:
d_in: The date the patient was admitted to the hospital
d_out: The date the patient was released from the hospital
t_stay: The total number of days the patient stayed at the hospital
t_cost: The total cost of the stay.
I need a way to get the t_stay and t_cost separated by month; for example, if a patient was admitted to the hospital from the 20th of april to the 10th of may and the total cost was $200, I would get the values stay_april=10, stay_may=10, cost_april=100 and cost_may=100.
If the patient is admitted and released the same month, it is easy, since I can extract the month of the start and the month of the end, check with an if-statement if they are the same, and if they are, I have the values I need directly from the t_stay and t_cost variables.
But I'm having trouble figuring out how to do this with patients whose stay span several months? I hope my question makes sense and I will be grateful for any help.
The following suggested code isn't fully tested and definitely wouldn't win any award for efficient programming. However, I think it will provide what you need to figure out how to solve your problem:
data have;
informat d_in d_out date9.;
format d_in d_out date9.;
input id d_in d_out t_cost;
cards;
123 20apr2007 21apr2007 1000
124 20apr2007 10may2007 4000
125 30apr2007 02jun2007 9000
;
data want(keep=id new:);
set have;
format new_d_in new_d_out date9.;
t_days=d_out-d_in;
per_day=t_cost/t_days;
no_months=intck('month',d_in-day(d_in)+1,
d_out-day(d_out)+1);
year=year(d_in);
month=month(d_in);
do i=1 to no_months+1;
if i eq 1 then do;
new_d_in=d_in;
if i eq no_months+1 then do;
new_d_out=d_out;
new_t_days=t_days;
end;
else do;
new_t_days=intnx('month',d_in,0,'E')-d_in+1;
new_d_out=intnx('month',d_in,0,'E')+1;
end;
end;
else if i gt 1 then do;
month+1;
if month gt 12 then do;
month=1;
year+1;
end;
if i eq no_months+1 then do;
new_d_in=intnx('month',d_out,0,'B');
new_t_days=day(d_out)-1;
new_d_out=d_out;
end;
else do;
new_t_days=
intnx('month',mdy(month,1,year),0,'E') -
mdy(month,1,year)+1;
new_d_out=intnx('month',mdy(month,1,year),0,'E')+1;
new_d_in=intnx('month',mdy(month,1,year),0,'B');
end;
end;
new_t_cost=per_day*new_t_days;
output;
end;
run;
It seems that you need intck() to check the number of interval.
But It would more helpful if you can post some dummy data and what output you need.
data _null_; a='20apr2009'd; b='10may2009'd; cost=100; x=intck('month',a-day(a)+1,b-day(b)+1); mon_cost=cost/x; put x= mon_cost=; run;
Ksharp
Hi Ksharp,
Thanks for the reply, I've looked into the intck() and intnx() and I think it's definately a step in the right direction. I'm still having trouble putting it all together though.
Dummy data of what I have:
Id d_in d_out t_cost t_days
123 20apr2007 21apr2007 1000 1
124 20apr2007 10may2007 4000 20
125 30apr2007 02jun2007 9000 66
Dummy data of what I would like the output to be:
Id d_in d_out t_cost t_days
123 20apr2007 21apr2007 1000 1
124 20apr2007 01may2007 2200 11
124 01may2007 10may2007 1800 9
125 30apr2007 01may2007 300 1
125 01may2007 01jun2007 9400 31
125 01jun2007 02jun2007 300 1
Anyone know how to achieve this?
The following suggested code isn't fully tested and definitely wouldn't win any award for efficient programming. However, I think it will provide what you need to figure out how to solve your problem:
data have;
informat d_in d_out date9.;
format d_in d_out date9.;
input id d_in d_out t_cost;
cards;
123 20apr2007 21apr2007 1000
124 20apr2007 10may2007 4000
125 30apr2007 02jun2007 9000
;
data want(keep=id new:);
set have;
format new_d_in new_d_out date9.;
t_days=d_out-d_in;
per_day=t_cost/t_days;
no_months=intck('month',d_in-day(d_in)+1,
d_out-day(d_out)+1);
year=year(d_in);
month=month(d_in);
do i=1 to no_months+1;
if i eq 1 then do;
new_d_in=d_in;
if i eq no_months+1 then do;
new_d_out=d_out;
new_t_days=t_days;
end;
else do;
new_t_days=intnx('month',d_in,0,'E')-d_in+1;
new_d_out=intnx('month',d_in,0,'E')+1;
end;
end;
else if i gt 1 then do;
month+1;
if month gt 12 then do;
month=1;
year+1;
end;
if i eq no_months+1 then do;
new_d_in=intnx('month',d_out,0,'B');
new_t_days=day(d_out)-1;
new_d_out=d_out;
end;
else do;
new_t_days=
intnx('month',mdy(month,1,year),0,'E') -
mdy(month,1,year)+1;
new_d_out=intnx('month',mdy(month,1,year),0,'E')+1;
new_d_in=intnx('month',mdy(month,1,year),0,'B');
end;
end;
new_t_cost=per_day*new_t_days;
output;
end;
run;
Thanks art297 and Ksharp, you guys rock and are now my favorite SAS gurus 🙂
data have; informat d_in d_out date9.; format d_in d_out date9.; input id d_in d_out t_cost; cards; 123 20apr2007 21apr2007 1000 124 20apr2007 10may2007 4000 125 30apr2007 02jan2008 9000 ; run; data want(drop=i n _: avg); set have; n=intck('month',d_in-day(d_in),d_out-day(d_out)+1); avg=t_cost/(d_out-d_in); _d_in=d_in; _d_out=d_out; do i=1 to n; d_in=_d_in; if i ne n then d_out=intnx('month',d_in,0,'e')+1; else d_out=_d_out; t_day=d_out-d_in;t_cost=avg*t_day; output; _d_in=d_out; end; run;
Ksharp
Message was edited by: xia keshan Sorry. There is a problem about calculating the number of months.
That's brilliant, thanks 🙂
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.