11-21-2016 02:03 PM
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?
11-21-2016 02:22 PM - edited 11-21-2016 02:23 PM
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..
11-21-2016 02:52 PM
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.
11-21-2016 03:17 PM
Strip the formats and show the data for the records that are negative.
Something like the following, post a portion of the log:
Where diff < 0;
format vist_date hospitaldate;
put 'Visit Date:' visit_date;
put 'Hospital Date:' hospital_date;
11-21-2016 03:25 PM
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.
11-21-2016 03:36 PM
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!
11-21-2016 03:58 PM
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".