DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

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

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)


Accepted Solutions
Solution
‎10-12-2016 07:57 AM
Super Contributor
Posts: 578

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

[ Edited ]

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.

 

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);

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,404

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

Hi,

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

Occasional Learner
Posts: 1

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

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;

*/

Super User
Posts: 9,681

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

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

Solution
‎10-12-2016 07:57 AM
Super Contributor
Posts: 578

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

[ Edited ]

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.

 

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);

 

New Contributor
Posts: 4

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

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.

Contributor
Posts: 23

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

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

Super Contributor
Posts: 578

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

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

Occasional Contributor
Posts: 17

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

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.

Trusted Advisor
Posts: 1,204

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

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;

New Contributor
Posts: 4

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

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.

Contributor
Posts: 23

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

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().

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 66899 views
  • 5 likes
  • 9 in conversation