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 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.