SAS date formats: How to display dates correctly?

SAS date and time are essential topics in SAS because SAS date and time have unique characteristics. SAS dates are not numeric, not character variables. When displayed, it resembles a character, although it is stored as a number. 

A SAS date begins from January 1, 1960, and has a value of 0. Before this dates are negative numbers and positive numbers after this date. SAS Formats are used to display values in various formats. There are several different SAS date formats that you can apply and make the dates readable.

[ Recommended Reading: The Yearcutoff System Option in SAS ]

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 ensure dates are stored correctly as date values while the value itself is stored as a number of days since January 1, 1960.

Here is an example showing what happens when you use the wrong date format.

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

This output will show a missing numeric 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 72 1-10.
 NOTE: Invalid data for dt2 in line 72 11-15.
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      72         04-22-20 APR2020 dt=. dt2=. _ERROR_=1 _N_=1

So, what went wrong?

The first step in investigating these errors is to check whether you have used the correct date format to read the date values.

For example, if the date informat used is mmddyy10. and the data value given is in the format of ddmmyy10.

The date format is correct, but the raw data value is eight digits wide instead of 10. Therefore, if you examine the actual column shown in the rule, you will see a mismatch where SAS is expecting a date in the format of mmddyy10. 

That means the width of the date informat for the dt1 variable is ten, and the monyy format specifies the format width of 5. (3 for month name and 2 for the year).

The above illustration shows a mismatch between the width of the column specified by the informat variable dt1 and the actual date value. The dt2 variable expects the date to begin from column 12 but begins from column 9. This is because only two digits are specified for the year part in dt1 variable.

How can you fix this?

You have identified a mismatch in the date informat and the column’s width; you can fix this in two ways.

The first solution is to use formatted input with a column pointer. This way, you can tell SAS explicitly which column and variable should begin.

input dt mmddyy10. @10. dt2 monyy.;

Another method is to use the correct width of the informat according to the raw data. That is if you use mmddyy8. Then, SAS will read the dates correctly.

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;

The log below with the date value in a numeric format: 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 must ensure that dates are correctly imported in SAS if the 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 before January 1, 1960, excel cannot.

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

Here is the snapshot input file in xlsx format.

To import the excel file in SAS, we will submit the following code.

proc import datafile= "/folders/myfolders/dates.xlsx" 
dbms = xlsx out=four replace;
run;

The date1 variable is a character date format, and the date4 variable is the number of days since the excel start date.

You can use the input function to convert the character date to the 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;

We were able to convert dt_gen from numeric date to SAS, but the years are not correct. This is because Excel stores the number, and SAS imports that number.

Since there is a difference in the default dates between SAS and Excel, you must convert the date and time variable of character dates to SAS dates using the formula below. 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_gen dates have been converted to SAS dates with correct values.

You need not be required to subtract 21916 from the date1 as because

Now, date1 and dt_gen dates have been converted to SAS dates with correct values.
You need not be required to subtract 21916 from the date1 because 24/04/1846 is before January 1, 1900.

SAS dates Formats

For displaying SAS dates, you need to use SAS date formats. As discussed, the SAS dates are stored as the number of days sing January 1, 1960, which is not helpful 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.

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

❷ Algorithm U calculates SAS date by using the number of weeks within the year. For example, Sunday is the first day of the week. The number of week values is represented in 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 weeks within the year, and Monday is considered the first day of the week. The week’s number is a decimal number in 0-53.