DATA Step, Macro, Functions and more

Calculating Difference between two dates returning a negative number

Reply
Contributor
Posts: 65

Calculating Difference between two dates returning a negative number

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:

2016-11-21_14-01-18.png

 

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

Super User
Posts: 17,818

Re: Calculating Difference between two dates returning a negative number

Just subtract the dates. 

 

 

Want = visit_date - hosp_date;

Super User
Posts: 17,818

Re: Calculating Difference between two dates returning a negative number

I wonder if you have datetime instead of data variable?

PROC Star
Posts: 551

Re: Calculating Difference between two dates returning a negative number

[ Edited ]

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 Smiley Happy

 

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

Contributor
Posts: 65

Re: Calculating Difference between two dates returning a negative number

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. 

Super User
Posts: 17,818

Re: Calculating Difference between two dates returning a negative number

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;

Super User
Posts: 10,497

Re: Calculating Difference between two dates returning a negative number

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.

Contributor
Posts: 65

Re: Calculating Difference between two dates returning a negative number

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!

Super User
Posts: 10,497

Re: Calculating Difference between two dates returning a negative number

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".

 

Ask a Question
Discussion stats
  • 8 replies
  • 425 views
  • 0 likes
  • 4 in conversation