Seperating Total Values Into Monthly Values

[tjoones]

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.

[Ksharp]

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

[tjoones]

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?

[art297 · Accepted solution]

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;

[tjoones]

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

[Ksharp]

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.

[tjoones]

That's brilliant, thanks 🙂