If you obtain data from web sites, social media, or other unstandardized data sources, you might not know the form of dates in the data. For example, the US Independence Day might be represented as "04JUL1776", "07/04/1776", "Jul 4, 1776", or "July 4, 1776." Fortunately, the ANYDTDTE informat makes it easy read dates like these into SAS.
The ANYDTDTEw. informat is a flexible alternative to older informats such as DATEw., MMDDYYw., and YYMMDDw. If your dates are in a specific form, the older informats work great and serve to document that all dates must be in that standard form. If the dates are not standardized or you need to read a string like "July 4, 1776", the ANYDTDTE informat is a godsend.
The ANYDTDTE informat for reading dates
The following SAS DATA step shows that the ANYDTDTEw. format combines several older formats into a "super format" that attempts to convert a character string into a date. The ANYDTDTE format can not only replace many of the older formats, but it can be used to convert a string like "Jul 4, 1776" into a date, as follows:
data Dates; input @1 Style $8. @9 Value anydtdte12.; format Value DATE10.; datalines; DATE 04JUL1776 MMDDYY 07041776 MMDDYY 07/04/1776 YYMMDD 17760704 N/A Jul 4, 1776 N/A July 4, 1776 ; proc print noobs; run; |
As you can see, the ANYDTDTE informat reads six different strings, but converts all of them to the SAS date value that corresponds to 04JUL1776.
MMDD or DDMM? How does ANYDTDTE interpret ambiguous dates?
The string 07/04/1776 can be interpreted as "April 7, 1776" or "July 4, 1776," depending upon the local convention. Europeans tend to interpret the string as DD/MM/YYYY whereas the US convention is to use MM/DD/YYYY. How does the ANYDTDTEw. informat guess which interpretation might be correct?
The answer is that the informat looks at the DATESTYLE SAS option. By default, the DATESTYLE option uses the LOCALE system option to guess which style to use. You can use PROC OPTIONS to see the value of these options, which are printed to the SAS log:
proc options option=(DATESTYLE LOCALE) value; run; |
Option Value Information For SAS Option DATESTYLE
Value: MDY
Scope: Default
How option value set: Locale
Option Value Information For SAS Option LOCALE
Value: EN_US
... |
For my system, the DATESTYLE option is set to MDY, which means that the string "07/04/1776" will be interpreted MM/DD/YYYY. If you need to read dates that obey a different convention, you can use the global OPTIONS statement to set the DATESTYLE option:
options DATESTYLE=DMY; /* change default style convention */ /* Restore default convention: options DATESTYLE=Locale; */ |
Other "ANY" informats in SAS
There are two other SAS infomats that are similar to the ANYDTDTE informat:
- The ANYDTDTMw. informat creates SAS datetime value from various input styles.
- The ANYDTTMEw. informat creates SAS time value from various input styles.
Here's a tip to help you remember these seemingly cryptic names. The first part of the name is "ANYDT", which means that the input string can be ANY datetime (DT) value. The end of the name refers to the numerical value that is produced by the informat. The resulting value can be a date (DTE), a datetime (DTM), or a time (TME) value. Thus the three informats all have the mnemonic form ANYDTXXX where the XXX suffix refers to the value that is produced.
12 Comments
A powerful and useful informat if your datestyle option is set the way you expect! I came across a situation a few years ago where a SAS user said the anydtdte informat would only work with MDY style dates and he reverted to the specific date style informats with additional data cleansing. I knew there would have to be an option to set the style or locale and in finding the datestyle and locale options we set them to DMY and EN_AU and he was a happy chappy. Spoke about this tip and other tips at our local SAS user group meeting later that year http://www.sascommunity.org/wiki/2013Q3-QUEST
That's the great thing I've found with the SAS language. If things don't work the way you expect, look for an option or another technique. There is usually a few ways to do something.
Thanks for the story. I should have mentioned that SAS dates are a HUGE topic at SAS conferences! In addition to your presentation, there are dozens of paper by SAS users such as Art Carpenter. An internet search for "SAS ANYDT informat" reveals a wealth of information on this topic.
What does this mean?
You need to define "this." What are you confused about?
Pingback: Visualize the ages of US presidents - The DO Loop
Hello there,
How to convert different types of date format in a single variable into one date format using Proc steps??
Thanks in advance.
Regards,
Vijay
Thanks for your comment. I believe that this post answers your question. If not, please post sample data and show what you have attempted at the SAS Support Communities.
Great post. Be very wary though with ANYDT style informats in Stored Process Web Applications - the locale can change depending on your client!! https://www.rawsas.com/2016/12/look-out-locale-gotcha/
Hi Rick,
Thank you for the insightful post. I'm having a challenge with handling date formats such as ,
Dec-15, which is, the month of December and year 2015. Please assist me. Thanks.
You can ask questions like this and post code at the SAS Support Communities. My initial comment is this blog post discusses how to read dates, but Dec-2015 is not a date. It is a month, so when you post your question you should clarify what you are trying to accomplish.
data hw13;
input id hire_date birth_date;
datalines;
101 8/12/2017 21-Aug-71
102 9/4/2017 30-Apr-75
103 10/28/2017 23-Apr-88
104 1/5/2018 18-Feb-75
105 10/31/2017 18-May-73
106 9/11/2017 04-Mar-72
107 1/27/2018 01-Dec-76
108 3/5/2018 26-Apr-72
109 2/23/2018 04-Jan-92
110 1/16/2018 29-Oct-82
;
run;
can not read this date
You can post SAS programming questions to the SAS Support Communities. (Hint: Use the INFORMAT statement, as shown in this blog post!)