DATA Step, Macro, Functions and more

Seperating Total Values Into Monthly Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Seperating Total Values Into Monthly Values

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.


Accepted Solutions
Solution
‎11-02-2011 11:34 AM
PROC Star
Posts: 7,364

Re: Seperating Total Values Into Monthly Values

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 newSmiley Happy;

  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;

View solution in original post


All Replies
Super User
Posts: 9,687

Re: Seperating Total Values Into Monthly Values

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

Occasional Contributor
Posts: 8

Re: Seperating Total Values Into Monthly Values

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?

Solution
‎11-02-2011 11:34 AM
PROC Star
Posts: 7,364

Re: Seperating Total Values Into Monthly Values

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 newSmiley Happy;

  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;

Occasional Contributor
Posts: 8

Re: Seperating Total Values Into Monthly Values

Thanks art297 and Ksharp, you guys rock and are now my favorite SAS gurus :-)

Super User
Posts: 9,687

Re: Seperating Total Values Into Monthly Values

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.

Occasional Contributor
Posts: 8

Re: Seperating Total Values Into Monthly Values

That's brilliant, thanks :-)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 177 views
  • 6 likes
  • 3 in conversation