BookmarkSubscribeRSS Feed
einstein
Quartz | Level 8

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?

8 REPLIES 8
Reeza
Super User

Just subtract the dates. 

 

 

Want = visit_date - hosp_date;

Reeza
Super User

I wonder if you have datetime instead of data variable?

PeterClemmensen
Tourmaline | Level 20

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

einstein
Quartz | Level 8

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. 

Reeza
Super User

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;

ballardw
Super User

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.

einstein
Quartz | Level 8

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!

ballardw
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 6995 views
  • 0 likes
  • 4 in conversation