BookmarkSubscribeRSS Feed
lmyers2
Obsidian | Level 7

Hello, I have 2 dates and I'm trying to get the difference between them in days. One is formatted as datetime20 and the other as date9. I've tried the following and neither works. 

 

Format them both as date9 --> Error message, there was a problem with the format so best. was used. Not sure what best. format is or whether I needed to change the informat as well. Is the difference between 2 date9 variables come out in units of days?

data want; set have;
format evrprdttm date9.;
informat evrprdttm date9.;
days=intck('dtday', evrprdttm, adate);
run;

 

Reformat them both as numeric --> the revised dates come out as missing along with "diff"

data want; set have;
adate_revised=input(adate, mmddyy10.);
evrprdttm_revised=input(evrprdttm, mmddyy10.);
diff=evrprdttm_revised-adate_revised;
run;

 

4 REPLIES 4
SASKiwi
PROC Star

I suggest you try converting your SAS datetime to a date first then calculate the difference as days. Also if your SAS date / datetime already has formats applied there is no reason to apply them again.

data want; set have;
days = intck('day', datepart(evrprdttm), adate);
run;
lmyers2
Obsidian | Level 7

Thanks SASKiwi - when I convert evrprdttm below from datetime20 to date9, I get lines and lines of the error message pasted just below it. In proc contents, the format converts successfully to date9 but in the data, the datetime July 3, 2020 6:15 ends up being in exponential form and the subtraction doesn't work.  If you have any ideas for why I keep getting this error, would greatly appreciate the help.

 

data hcuant; set more.hcu;
format evrprdttm date9.;
days=intck('day', datepart(evrprdttm), datepart(adate));
run;

ERROR: There was a problem with the format so BEST. was used.

SASKiwi
PROC Star

I think we need to confirm what the input dataset really contains. Run this and post the results.

proc contents data = more.hcu;
run;
Patrick
Opal | Level 21

A format only defines how a value gets printed. It does not change the actual internal value. SAS Dates and Datetime values are counts of days/counts of seconds since 1/1/1960.

Any function like datepart() or intck() works on the internal values irrespective of the format. 

 

If evrprdttm really contains a SAS Datetime value and adate a SAS Date value then below should work:

data hcuant; 
set more.hcu; days=intck('day', datepart(evrprdttm), adate); run;

...and because SAS Date values are just counts of days since 1/1/1960 also below code will work for SAS Date values:

data hcuant; 
  set more.hcu;
  days=adate - datepart(evrprdttm);
run;

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 2005 views
  • 0 likes
  • 3 in conversation