How to calculate difference in days between two dates (given in SAS date format)?

[viollete]

Hello,

Example of data:

ID Date_1 Date_2

1   x1

1   x1

1  x1          x2

2  x3

2  x3         x4

ID represent individual, Date_1 the day when that individual came to hospital, Date_2 - the day when he left it. Individual have several rows due to fact that he used several procedure.

How could I calculate the number of days he stayed in hospital? (date is given SAS data format)

[RW9]

Hi,

Check out the intck function.SAS(R) 9.3 Functions and CALL Routines: Reference

[dgammon]

This worked for me when I had to datetime variables(date1 & date2) and wanted the differnce between dates in days

/*

data test;
set test;
by subj;
   days=intck('dtday', date1, date2);
   put days=;
run;

*/

[Ksharp]

What is your real data ? and the output you like to see ?

[DBailey · Accepted solution]

Editor's note: This is a popular topic. We have summarized the best answers into this single reply to help you find the answer you need. But first, here's a video that addresses common questions around SAS date functions. Since it's 30+ minutes long, below are timestamps to guide you to the tip relevant to you:

00:16 – SAS Dates Explored

06:07 – Example: How to convert a character form of a date to a SAS Date value using the SAS INPUT function

16:46 – Example: How to determine time passed between two dates using the YRDIF function

19:08 – SAS Times Explored

22:15 – Example: How to convert a character form of a time to a SAS Time value using the SAS INPUT function

26:36 – SAS DateTime Explored

32:04 – Example: How to convert a character form of a DateTime to a SAS DateTime value using the SAS INPUT function

Related content

• Paper: The Essentials of SAS Dates and Times
• Book: SAS Functions by Example, Second Edition
• Community article: INTNX and INTCK Function Examples
• Blog post: Do you mind if we dance with your DATEs (or DATETIMEs)?

Below are aggregated "best answers" to this community topic

From @cov_derek: 

"SAS-formatted dates" are stored as an integer representing numer of days relative to January 1, 1960. This makes it easy to do simple calculations involving dates.  In general, the difference between two SAS dates in days can most easily be calculated as duration=end_date - start_date.

Sample SQL code to calculate:

proc sql;
create table Stay as
select
     t1.id,
     min(date_1) as Admit_Date,
     max(date_2) as Discharge_Date,
     max(date_2) - min(date_1) as Length_of_stay
from
     have t1
group by t1.id;
quit;

You'd have to decide how to handle the case where patients were discharged the same day they were admitted.  

The INTCK function in SAS can calculate the difference between any two dates or datetime values, and return whatever interval you're looking for (days, minutes, hours, weeks, months).  @dgammon shares this example of using INTCK with datetime variables to calculate a day interval.

data test;
set test;
by subj;
   days=intck('dtday', date1, date2);
   put days=;
run;

For hospital stays, you might have special rules about calculating day intervals.  For example, a patient who is admitted and charged on the same day might count as a 1-day stay, and a patient who is discharged on the second day might still count as a 1-day stay -- so you might need to modify the formula to add 1 in the case of a same-day discharge.  If your data are such that any record with a hospital admission represents at least a 1-day stay (for reporting purposes), then your formula might be:

/* return the higher of two values: calculated interval or 1 */
dur = max(intck('day',date_1,date_2), 1);

[falcon69]

If you are using SAS-formatted dates, you should use the INTCK function to calculate the interval, e.g., INTCK(DAY, Date_1, Date_2) to calculate the number of days, or INTCK(HOUR,Date_1, Date_2) to calculate the number of hours.

[cov_derek]

Hi violette,

The first thing to remember is that "SAS-formatted dates" are stored as an integer representing numer of days relative to January 1, 1960. This makes it easy to do simple calculations involving dates.

In general, the difference between two SAS dates in days can most easily be calculated as duration=end_date - start_date. If you want to consider people who (in this case) are admitted and discharged on the same date as having a duration of 1 day, then the formula becomes duration=end_date-start_date+1. Using the INTCK() function for something this simple is overkill, as it counts interval boundaries and not necessarily contiguous days. In this specific case, this isn't an issue, but it can cause confusion if you are using date intervals other than days. With aplogies to falcon69, using the INTCK function to calculate the number of hours between two dates is incorrect because the HOUR interval is only applicable to times.

Alternately, you can use the DATDIF() function to calculate the difference, but unless you need to use 30-day standard months and 360-day standard years, this is identical to subtracting start_date from end_date.

Hope this helps,

Derek

[DBailey]

I don't think the formula duration=end_date - start_date + 1 will work.  If I remember my hospital reporting days...both of these cases should evaluate to a duration of one

Admin     Discharge

01Jul14     01Jul14

01Jul14     02Jul14

[bbenbaruch]

I agree w/ cov_derek.  This is a simple arithmetic solution.  If x1 and x2 are SAS date variables, then number of days in hospital is either x2-x1 OR IF YOU WANT TO BE INCLUSIVE of both the admit and discharge dates then (x2-x1)+1.

If x1 and x2 are datetime variables, then the solution is similarly simple but you have to adjust for the fact that datetime variables are the number of seconds since midnight, Jan. 1, 1960 ('01JAN1960 00:00:00'dt).  There are therefore 2 ways of handling this.

     (1) Convert x1 and x2 to date variables:  X1_d = DATEPART(X1) ;  X2_d =  DATEPART(X2) ;

     (2) Convert x1 and x2 to days "on the fly" by dividing each variable by the number of seconds in a day:  (X2/(60*60*24)) - (X1/(60*60*24))

Don't forget to apply your favorite date or datetime FORMAT to your variables.

Modify with +1 if you want to be inclusive of both the admission and discharge days.

[stat_sas]

Hi,

Try this to calculate number of days while individual remained in the hospital.

data have;
input id (Date_1-Date_2) (: mmddyy10.) ;
format Date_1-Date_2 mmddyy10.;
cards;
1     12/01/2014  12/14/2014
1     01/01/2014  01/06/2014
1     01/01/2014  01/06/2014
1     10/01/2012  10/05/2012
2     01/01/2014  01/05/2014
2     12/01/2014  12/03/2014
2     12/01/2014  12/07/2014
2     02/11/2014  02/15/2014
2     01/01/2014  01/11/2014
;
run;

proc sql;
select id,sum(INTCK('DAY',Date_1,Date_2)) as days
from have
group by id;
quit;

[falcon69]

If you want to write code that does what INTCK does, that's cool.  It's fun.  Lots of ways to do this. However, when SAS provides a function, most likely it is more efficient than code we would write, plus INTCK doesn't care whether the date variable is seconds, minutes hours, days, etc.  For example, if the login recorded at the hospital is at the minutes level, which would correspond to most sign-ins I've done at a clinic, then INTCK(HOURS,date1,date2) will work nicely. If you want to convert hours to days, just use the CEIL function; e.g., DAYS=CEIL(HOURS, Date1, date2)/24).  That way you could have queue service time for walk-ins as well as hospital stay time in days.

[cov_derek]

INTCK() DOES care whether the data variable is is seconds, etc. More specifically, it cares whether the value is a datetime value or a date value.

There is an enormous difference between days since 1/1/60, and seconds since midnight, 1/1/60. Given that the original question represented dates, using the HOURS interval with date values will yield an incorrect answer. If you want days from datetime values using INTCK, then you must use the DTDAY interval - it's less efficient to use two SAS functions AND a mathematical calculation. And then there's the question of which method you want to use: SAS 9 gives you the option to count interval boundaries (as it has historically) or continuous elapsed time with INTCK().