BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tjoones
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

6 REPLIES 6
Ksharp
Super User

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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

Ksharp
Super User
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
Calcite | Level 5

That's brilliant, thanks 🙂

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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