topic Re: Seperating Total Values Into Monthly Values in SAS Programming

Seperating Total Values Into Monthly Values

tjoones — Wed, 02 Nov 2011 04:54:40 GMT

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 20^th of april to the 10^th 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.

Re: Seperating Total Values Into Monthly Values

Ksharp — Wed, 02 Nov 2011 08:50:42 GMT

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

Re: Seperating Total Values Into Monthly Values

tjoones — Wed, 02 Nov 2011 12:35:29 GMT

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?

Re: Seperating Total Values Into Monthly Values

art297 — Wed, 02 Nov 2011 15:34:21 GMT

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;

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;

new_t_cost=per_day*new_t_days;

output;

end;

run;

Re: Seperating Total Values Into Monthly Values

tjoones — Wed, 02 Nov 2011 16:43:04 GMT

Thanks art297 and Ksharp, you guys rock and are now my favorite SAS gurus 🙂

Re: Seperating Total Values Into Monthly Values

Ksharp — Thu, 03 Nov 2011 07:18:34 GMT

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.

Re: Seperating Total Values Into Monthly Values

tjoones — Thu, 03 Nov 2011 12:10:01 GMT

That's brilliant, thanks 🙂