SAS date formats: How to display dates correctly?

SAS date and time are one of the important topics in SAS because SAS date and time have unique characteristics. SAS dates are not numeric not character variable, when displayed it resembles character but it is stored as a number. SAS date begins from January 1, 1960, and has a value of 0. Before this date is negative numbers and those after this date are positive numbers. SAS Formats are used to display values in various formats. There is several different SAS date formats which you can apply and make the dates readable.

Every SAS users, regardless of industry, need to use SAS dates which can include displaying dates, reading dates from raw files, sorting dates, converting dates from one format to another, calculating time intervals between two dates and many more.

I will start with exploring dates in SAS, the Yearcutoff System option, reading SAS dates from excel and displaying them in human-readable formats.

Reading SAS dates

You can use SAS date informats to read SAS dates from an input file. A date informat tells SAS to read dates as specified in the informat and ensuring dates are stored correctly as date values while the value itself is stored as a number which is the number of days since January 1, 1960.

Here is an example showing if wrong date informat is used.

data one; 
input dt mmddyy10. dt2 monyy.; 
datalines; 
04-22-2020 APR20 ; 
run;

The output for this will show a numeric missing value (.) for the dt variable. In this case, you need to read the SAS log to see what might have gone wrong while reading the SAS date.

Here is the log message indicating a NOTE.

NOTE: Invalid data for dt in line 74 1-10.
NOTE: Invalid data for dt2 in line 74 11-15.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
74 04-22-20 APR20
dt=. dt2=. ERROR=1 N=1

So, what went wrong?

The first step of investigation these errors is to check whether the right date format is used to read the date values. Example if the date informat used is mmddyy10. and the data value given is in the format of ddmmyy10.

In this, the date format used is in the correct form but the raw data value is 8 digits wide instead of 10. If you examine the actual column shown in the rule, you will see a mismatch in the column where SAS is expecting a date in the format of mmddyy10. that means the width of the date informat for dt1 variable is 10 and monyy. specifies the format width of 5. (3 for month name and 2 for the year).

SAS date formats

From, the above image, you can make out that there is a mismatch between the width of the column specified by the informat dt1 and the actual date value. The dt2 variable expects the date to begin from column 12 but it begins from column 9. This is because only 2 digits are specified for the year part in dt1 variable.

How can you fix this?

Now, you have identified that there is a mismatch of the format of date informat and the width of the column, you can fix this in two ways. The first solution is to use formatted input with a column pointer. In this way, you can tell SAS explicitly which column which variable should begin.

input dt mmddyy10. @10. dt2 monyy.;

The next way is to use the correct width of the informant according to the raw data. That is if you use mmddyy8. SAS will read dates correctly.

Yearcutoff System Option

The YEARCUTOFF= option lets you specify what century SAS should assign to dates when two-digit years are used. The default year cutoff is 1926. You can specify yearcutoff option as below.

options yearcutoff=1920;

You can also specify the option in an Autoexec file or a config file.

How to see the yearcutoff value set in your SAS session?

PROC OPTIONS procedure can be used to see the system options in SAS Log. You can specify yearcutoff in the option= to see only the value for yearcutoff.

proc options option=yearcutoff;
run;

After running the above code, the following message is displayed in the SAS log.

YEARCUTOFF=1926 Specifies the first year of a 100-year span that is used by date informats and functions to read a two-digit
year.

How does the YEARCUTOFF= option work?

The YEARCUTOFF= option specifies the first year of a 100 year window in which all 2-digit years are assumed to be. For example, if the YEARCUTOFF= option is set to 1926, all 2-digit years are assumed to be in the period 1926 through 2026.

This means that two-digit years from 26-99 will be assigned a century prefix of ’19’ and all 2-digit years from 00-25 will have a century prefix of ’20’. Note that, the year 2026 is exclusive.

Example:

data two;
input dt2 monyy7.;
format dt2 monyy7.;
datalines;
APR96
APR20
APR2020
APR25
APR26
APR27
APR2027
;
run;
SAS date formats: How to display dates correctly? 1

As you can see for APR26 and APR27, the SAS has considered 1926 and 1927, but what was expected was 2026 and 2027. This is because the 26 and 27 falls in the range of 26 to 99 which is assigned year prefix of 19. Also, note that 4 digit year values are not being affected by the yearcutoff option.

If you update the yearcutoff value as below, you will get the result as expected.

options yearcutoff=1930;

The YEARCUTOFF= option affects the interpretation of two-digit years in the following cases:

  • When reading date values from external files.
  • If you specifying dates or year values in SAS functions.
  • If you are specifying SAS date literals

The YEARCUTOFF= option does not affect in the following cases:

  • If you are processing dates with 4-digit years. Example of 2004.
  • If you are Processing dates that are already stored as SAS date values in numeric. (Example the number of days since January 1, 1960)
  • When displaying dates with SAS date formats.

Creating SAS date constants

You can create SAS date constants using the form ‘ddmmmyy’d or 'ddmmmyyyy'd. The quotes are necessary as well as the letter ‘d’.

Example:

data three;
dt1='20APR20'd;
dt2='20APR2020'd;
put _all_;
run;

And here is the log below with the date value in a numeric format that is the number of days since January 1, 1960.

dt1=22025 dt2=22025 ERROR=0 N=1

Importing dates from Excel to SAS

Generally, PROC IMPORT is preferred while importing Date from excel to SAS. You need to make sure that dates are correctly imported in SAS if Excel date is stored as ‘General Format’. Excel default date starts from January 1, 1900, and SAS dates start from January 1, 1960. While SAS can include negative number dates which are before January 1, 1960, but excel cannot.

Therefore, a date before January 1, 1900, will be missing data unless the field is stored as a character variable.

Here is the input file in xlsx format.

SAS date formats: How to display dates correctly? 3
proc import datafile= "/folders/myfolders/dates.xlsx" dbms = xlsx out=four replace;
run;
SAS date formats: How to display dates correctly? 5

You can see, the date1 variable is character date format, date4 is imported as numbers of days since excel start date.

You can use the input function to convert character date to actual date value followed by the date informat. in SAS. So, what happens when you try to convert date1 and date4 to SAS date format.

data inp;
set four;
date1 = input(date1,ddmmyy10.); /*converting character date to sas numeric date*/
format date4 date1 dt_gen date9.;
run;
SAS date formats: How to display dates correctly? 7

I was able to convert dt_gen from numeric date to SAS, but you can see the years are not correct. The reason being excel has stored the number and that number has been imported by SAS.

Since there is a difference on the default dates between SAS and Excel, you have to convert date and time variable of character dates to SAS date by using the below formula. Only use this formula if the excel date is on or after January 1, 1900.

SAS date = Excel date - 21916
SAS Time = Excel time * 86400;
SAS date and Time = (Excel date time - 21916) * 86400
data inp;
set four;
s_date1 = input(date1,ddmmyy10.);
sas_dt_gen = dt_gen-21916;
format s_date1 date4 sas_dt_gen date9.;
run;

Now, date1 and dt_gendates has been converted to SAS date with correct values

You need not required to subtract 21916 date1 as 24/04/1846 because this date is before to January 1, 1900.

SAS date formats

SAS dates Formats

For displaying SAS dates, you need to use SAS date formats as discussed previously the SAS dates are stored as the number of days sing January 1, 1960, which is not useful for readability. Just as SAS date informats are used to read dates, Formats are used for displaying SAS date values

SAS date formats also end with period like informats. Some of the frequently used SAS date formats are as below.

SAS Date FormatDefault widthRangeValue of SAS dateExampleResultDescription
DATE.75-924th April 2020DATE9.24APR20Date values in the form ddmmmyy, ddmmmyyyy, or dd-mmm-yyyy.
DAY.22-3224th April,2020DAY.24Date values as the day of the month.
DDMMYY.82-824th April,2020DDMMYY10.24/04/2020Writes SAS date values in the form ddmmyy or dd/mm/yy.
dd is an integer that represents the day of the month.
/ is the separator.
mm is an integer that represents the month.
yy is a two-digit or four-digit integer that represents the year.
DDMMYYX.82-1024th April,2020DDMMYYC10.24.04.2020Writes SAS date values in the form ddmmyy or ddxmmxyy
dd is an integer that represents the day of the month.
x is a specified separator. *
mm is an integer that represents the month.
yy is a two-digit or four-digit integer that represents the year.
DOWNAME91-3224th April,2020DOWNAME3.FridayDate values as the name of the day of the week.
MMDDYY.82-1024th April,2020MMDDYY10.04/24/2020Writes SAS date values in one of the following forms: mmddyy mm/dd/yy
mm is an integer that represents the month.
/ is the separator.
dd is an integer that represents the day of the month.
yy is a two-digit or four-digit integer that represents the year.
MMDDYYX.82-1024th April,2020MMDDYYD10.04-24-2020Writes SAS date values in one of the following forms: mmddyy mmxddxyy
mm is an integer that represents the month.
x is a specified separator.
dd is an integer that represents the day of the month.
yy is a two-digit or four-digit integer that represents the year.
MMYYX.75-3224th April,2020MMYYD.04-2020Writes SAS date values in one of the following forms: mmyy mmxyy
mm is an integer that represents the month.
x is a specified separator.
yy is a two-digit or four-digit integer that represents the year.
MMYY.75-3224th April,2020MMYY6.4M20Writes SAS date values in the form mmMyy
mm is an integer that represents the month.
M is the character separator.
yy is a two-digit or four-digit integer that represents the year.
MONAME.91-3224th April,2020MONAME1.ADate values as the name of the month.
MONTH.21-2124th April,2020MONTH.4Writes the month (1 through 12) of the year from a SAS date value.
MONYY.55-724th April,2020MONYY7.;APRIL2020Writes SAS date values in the form mmmyy or mmmyyyy
mmm is the first three letters of the month name.
yy or yyyy is a two-digit or four-digit integer that represents the year.
WEEKDATE.293-3724th April,2020WEEKDATE15.Fri, Apr 24, 20Writes SAS date values in the form day-of-week, month-name dd, yy (or yyyy):
dd is an integer that represents the day of the month.
yy or yyyy is a two-digit or four-digit integer that represents the year.
WEEKU.113-120024th April,2020WEEKU3.W16Week number in decimal format by using the U algorithm. *
WEEKV.113-120024th April,2020WEEKV6.20W17Week number in decimal format by using the V algorithm. *
WEEKW.113-20024th April,2020WEEKW.2020-W16-05Week number in decimal format by using the W algorithm. *
WEEKDAY.11-3224th April,2020WEEKDAY.6Writes a SAS date value as the day of the week (where 1=Sunday, 2=Monday, and so on).
WEEKDATX.293-3724th April,2020WEEKDATX3.FriDate values as the day of the week and date in the form day-of-week, dd month-name yy (or yyyy).
dd is an integer that represents the day of the month.
yy or yyyy is a two-digit or a four-digit integer that represents the year.
WORDDATE.183-3224th April,2020WORDATE.April 24,2020Date values as the name of the month, the day, and the year in the form month-name dd, yyyy.
WORDDATX.293-3724th April,2020WORDDATX.24 April,2020Date values as the day, the name of the month, and the year in the form dd month-name yyyy.
YEAR.42-3224th April,2020YEAR2.20Date values as the year.
YYMM.75-3224th April,2020YYMM5.20M04Date values in the form yyMmm
yy is a two-digit or four-digit integer that represents the year.
M is the character separator to indicate that the number of the month follows.
mm is an integer that represents the month.
YYMMX.75-3224th April,2020YYMMS.2020/04Date values in the form yymm or yy-mm.
yy is a two-digit or four-digit integer that represents the year.
x is a specified separator.
mm is an integer that represents the month.
YYMMDD.82-1024th April,2020YYMMDD6.200424Date values in the form yyMmm
M is a character separator to indicate that the month number follows the M and the year appears as either 2 or 4 digits.
YYMMDDX.82-1024th April,2020YYMMDDB.20 04 24Date values in the form yymmdd or yy-mm-dd
x in the format name is a character that represents the special character that separates the year, month, and day.
YYMON.75-3224th April,2020YYMON5.24 APRDate values in the form yymmm or yyyymmm.
yy is a two-digit or four-digit integer that represents the year.
mmm is the name of the month, abbreviated to three characters.
YYQ.64-3224th April,2020YYQ4.07Q1Date values in the form yyQq
yy is a two-digit or four-digit integer that represents the year.
Q is the character separator.
q is an integer (1,2,3, or 4) that represents the quarter of the year.
YYQX.64-3224th April,2020YYQS32.2020/2Date values in the form yyq or yy-q
yy is a two-digit or four-digit integer that represents the year.
x is a specified separator.
q is an integer (1,2,3, or 4) that represents the quarter of the year.
YYQR.86-3224th April,2020YYQRS8.2020/IIDate values in the form yyQqr
qr is a roman numeral (I, II, III, or IV) that represents the quarter of the year.
YYQRX.86-3224th April,2020YYQRC6.20:IIdate values in the form yyqr or yy-q
yy is a two-digit or four-digit integer that represents the year.
x is a specified separator.
QR is a roman numeral (I, II, III, or IV) that represents the quarter of the year.

Algorithm U calculates SAS data by using the number of week within the year. Sunday is the first day of the week. The number of week value is represented in the range of 0-53.

Algorithm V calculates the number of weeks considering Monday as the starting day of the week and is represented in the range between 01-53. Weeks that begin on a Monday and week 1 of the year include January 4 and the first Thursday of the year. If the first Monday of January is 2,3 or 4, the preceding days are considered as part of the last week of the previous year.

Algorithm W uses the current year as the year expression. If the input does not contain a day expression, then the first day of the week is used as the day expression. It calculates the number of week within the year and Monday is considered the first day of the week. The number of the week is represented as a decimal number in the range of 0-53.

The x in the format name represents the special character that separates the year and the month. This special character can be a hyphen (-), period (.), slash (/), colon (:), or no separator.

  • C separates with a colon.
  • D separates with a hyphen.
  • N indicates no separator.
  • P separates with a period.
  • S separates with a forward slash.

 

Subhro Kar

Been in the realm with the professionals of the IT industry. I am passionate about Coding, Blogging, Web Designing and deliver creative and useful content for a wide array of audience.

Click Here to Leave a Comment Below

Leave a Comment: