topic Re: Calculating Difference between two dates returning a negative number in SAS Programming

Calculating Difference between two dates returning a negative number

einstein — Mon, 21 Nov 2016 19:03:50 GMT

I am calculating the difference between two dates using the INTCK and DATDIF functions:

days_btwn_hosp= intck('day', a_hosp_dt,visit_date);
days_btwn_hospx=datdif(a_hosp_dt, visit_date, 'act/act');

It seems to generally work except a few are producing a negative number when the difference in dates should be positive, for example:

Does anyone know why this would happen? And how to fix it?

Re: Calculating Difference between two dates returning a negative number

Reeza — Mon, 21 Nov 2016 19:22:10 GMT

Just subtract the dates.

Want = visit_date - hosp_date;

Re: Calculating Difference between two dates returning a negative number

PeterClemmensen — Mon, 21 Nov 2016 19:23:15 GMT

Try running this code..

data have;
   input a_hosp_dt visit_date;
   informat a_hosp_dt visit_date mmddyy8.;
   format a_hosp_dt visit_date mmddyy8.;

   days_btwn_hosp= intck('day', a_hosp_dt,visit_date);
   days_btwn_hospx=datdif(a_hosp_dt, visit_date, 'act/act');

   datalines;
   03/14/15 03/20/15
   08/05/15 08/06/15 
   ;

It seems to be a problem with how your data is stored because your code is ok 🙂

Or as @Reeza says, just subtract the dates..

Re: Calculating Difference between two dates returning a negative number

Reeza — Mon, 21 Nov 2016 19:37:12 GMT

I wonder if you have datetime instead of data variable?

Re: Calculating Difference between two dates returning a negative number

einstein — Mon, 21 Nov 2016 19:52:24 GMT

I tried subtracting the dates and still get a negative number for just a few. I also tried changing the formats to mmddyy8. but that doesn't seem to change anything. What's odd is that this is only happening for a few cases, the rest are calculating correctly.

Re: Calculating Difference between two dates returning a negative number

Reeza — Mon, 21 Nov 2016 20:17:23 GMT

Strip the formats and show the data for the records that are negative.

Something like the following, post a portion of the log:

data check;

set want;

Where diff < 0;

format vist_date hospitaldate;

put 'Visit Date:' visit_date;

put 'Hospital Date:' hospital_date;

run;

Re: Calculating Difference between two dates returning a negative number

ballardw — Mon, 21 Nov 2016 20:25:49 GMT

Format has nothing to do with the value, just how the value is displayed.

Order of appearance in INTCK is important.

data _null_;
   x = intck('day', '01JAN2016'd, '20JAN2016'd);
   y = intck('day', '20JAN2016'd, '01JAN2016'd);
   put x= y=;
run;

Negative values indicate the the first parameter is larger (later ) than the second.

If you get this behavior for "just a few" I suggest that your initial data is flawed, likely from data entry. I would pull those records with the negative values and look for a pattern such as type of procedure. It may also be that a single admittance date is associated with multiple procedures.

I am wondering if anywhere in your process you are modifying either a_hosp_date or visit_date After calculating your days_betwen variables as the values you show are not possible from the dates shown.

You might want to show the entire data step code for calculating the days_between instead of just those two lines.

Re: Calculating Difference between two dates returning a negative number

einstein — Mon, 21 Nov 2016 20:36:41 GMT

I just asked our analyst to resend the raw file and with the new file, the difference in dates are correct now. In either case, it was strange to see negative numbers and I appreciate everyones help on this!

Re: Calculating Difference between two dates returning a negative number

ballardw — Mon, 21 Nov 2016 20:58:14 GMT

I have some data files from 1994 that I inherited as part of project. Some of the clinic visit "dates" were nearly 30 years in the future and we had some visits that occured 70 years before the program began. Or at least that's what was in the database extract. Sloppy data entry and entry programs that miss constraints like "patient release date cannot be before admit date".