BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gsk
Obsidian | Level 7 gsk
Obsidian | Level 7

Is there a quick way to compare two dates but only up to the same lengths, whichever the shorter length is between the two?

 

For example, if we compare date A and date B, and if date A is 2019-07-08 and date B is 2019-07-08T10:30, then SAS would say date B is bigger after comparing 2019-07-08 and 2019-07-08T10:30. Can we ask SAS to compare only up to length=10 (compare 2019-07-08 vs. 2019-07-08 instead of 2019-07-08 vs. 2019-07-08T10:30)?

 

This rule would apply to any lengths of the two dates in the same format: if we have two dates 2019-07-08T08:09:11 and 2019-07-08T08:08, then SAS would drop seconds for the first date and compare 2019-07-08T08:09 vs 2019-07-08T08:08. 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Use the colon modifier in your comparison, assuming you have character variables. 

 

if date1 =: date2 then do;

.....


end;

You can compare only a specified prefix of a character expression by using a colon (:) after the comparison operator. SAS truncates the longer value to the length of the shorter value during the comparison. In the following example, the colon modifier after the equal sign tells SAS to look at only the first character of values of the variable LastName and to select the observations with names beginning with the letterS:

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p00iah2thp63bmn1lt20esag14lh.htm&docsetVe...

View solution in original post

11 REPLIES 11
novinosrin
Tourmaline | Level 20

HI @gsk  Are your date and datetime variables numeric variables formatted in the way you have shown or those are character variables. Can you check using proc contents plz

gsk
Obsidian | Level 7 gsk
Obsidian | Level 7

Thank you for the response! They are both character variables with length=20. Dates are in 2019-07-12 format, and hour, min, and sec come after T with colons (i.e. T14:29:30) if there is any. There doesn't need to be all components of year, month, date, hour, min or sec. A date could have only the year and month or can be missing. 

Reeza
Super User

Use the colon modifier in your comparison, assuming you have character variables. 

 

if date1 =: date2 then do;

.....


end;

You can compare only a specified prefix of a character expression by using a colon (:) after the comparison operator. SAS truncates the longer value to the length of the shorter value during the comparison. In the following example, the colon modifier after the equal sign tells SAS to look at only the first character of values of the variable LastName and to select the observations with names beginning with the letterS:

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p00iah2thp63bmn1lt20esag14lh.htm&docsetVe...

gsk
Obsidian | Level 7 gsk
Obsidian | Level 7

Thank you everyone!

 

Either input(dateA, yymmdd10.)=input(dateB,yymmdd10.) or dateA=:dateB works

 

In the end, I did strip(dateA)=:strip(dateB). Thanks a lot! 

ballardw
Super User

Make sure that your dates, times and datetime variables are actual SAS date, time or datetime variables. These would show in proc contents or the data set description as 1) numeric values and 2) a format similar to Date9, MMDDYY10, YYMMDD10 for dates, TIME. for time values and DATETIME or E8601 and such.

 

Then when you need to compare "bits" of the values there are functions such as

 

If datevariable = datepart (datetimevariable)

if timevariable = timepart (datetimevariable)

 

or even use of the date functions:

 

If 12 = month(datepart(datetimevariable)); To get December records.

novinosrin
Tourmaline | Level 20

Hi @gsk   Why not doing it clean with a yymmdd10. informat?

 

data test;
date_A='2019-07-08';
date_B= '2019-07-08T10:30';
if input(date_A,yymmdd10.)=input(date_B,yymmdd10.) then output;
run;
gsk
Obsidian | Level 7 gsk
Obsidian | Level 7
But there could be cases like 2019-07-08T03 vs 2019-07-08T03:08 or 2017-03-14T21:33:20 vs 2017-03-14T21:33.
Reeza
Super User
Did you try my solution?
novinosrin
Tourmaline | Level 20

Hi @gsk  So what. You are only comparing the first 10 chars in your words length=10 and I would call them a width of 10 in a informat, Therefore what is after 10 chars in my opinion is garbage for SAS's informat yymmdd10.

 

And the date check becomes real and safe

 

data test;
date_A='2019-07-08T03 ';
date_B= '2019-07-08T03:08 ';
if input(date_A,yymmdd10.)=input(date_B,yymmdd10.) then output;
run;
gsk
Obsidian | Level 7 gsk
Obsidian | Level 7
Sorry, not yet! I will in a bit!
art297
Opal | Level 21

I agree with @Reeza's suggestion but, to account for leading and trailing blanks, I think you will want to add the strip function to the variables you're comparing. E.g., try the following code:

data have;
  input (date1 date2) ($20.);
  if _n_ eq 3 then date1=' '||trim(date1);
  if date1 =: date2 then x=1;
  if trim(date1) =: trim(date2) then y=1;
  if strip(date1) =: strip(date2) then z=1;
  cards;
2019-07-08T08:08:11 2019-07-08T08:08
2019-07-08T08:08    2019-07-08T08:08:11
2019-07-08T08:08    2019-07-08T08:08:11
;
run;

Art, CEO, AnalystFinder.com

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 11 replies
  • 2342 views
  • 8 likes
  • 5 in conversation