# 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 that 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.

Page Contents

## 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 investigating these errors is to check whether the right date format is used 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.

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

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.

## 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 <em>ERROR</em>=0 <em>N</em>=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.

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

You can see, the date1 variable is character date format, date4 is imported as 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; |

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 in the default dates between SAS and Excel, you have to convert the date and time variable of character dates to SAS dates 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 be required to subtract 21916 date1 as 24/04/1846 because this date is before January 1, 1900.

## 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 Format |
Default width |
Range |
Example |
Result |
Description |

DATE. | 7 | 5-9 | DATE9. | 24APR20 | ddmmmyy, ddmmmyyyy, or dd-mmm-yyyy. |

DAY. | 2 | 2-32 | DAY. | 24 | Day of the month. |

DDMMYY. | 8 | 2-8 | DDMMYY10. | 24/04/2020 | ddmmyy or dd/mm/yy. dd is the day of the month. / is the separator. mm is integrer month. yy is 2/4 digit year |

DDMMYYX. | 8 | 2-10 | DDMMYYC10. | 24:04:2020 | ddmmyy or ddxmmxyy dd is day of the month. x is a specified separator. * ❶mm is integer month. yy is 2/4 digit year. |

DOWNAME | 9 | 1-32 | DOWNAME3. | Friday | name of the day of the week. |

MMDDYY. | 8 | 2-10 | MMDDYY10. | 04/24/2020 | mmddyy mm/dd/yy mm is integer month. / is the separator. dd is the day of the month. yy is 2/4 digit year |

MMDDYYX. | 8 | 2-10 | MMDDYYD10. | 04-24-2020 | mmddyy mmxddxyy mm is integer month x is a specified separator. dd is the day of the month. Yy is 2/4 digit year. |

MMYYX. | 7 | 5-32 | MMYYD. | 04-2020 | mmyy mmxyy mm is Integer Month x is separator. yy is 2/4 digit year |

MMYY. | 7 | 5-32 | MMYY6. | 4M20 | mmMyy mm Integer month. M is the character separator |

MONAME. | 9 | 1-32 | MONAME1. | A | Date values as the name of the month. |

MONTH. | 2 | 1-21 | MONTH. | 4 | Month (1 through 12) of the year |

MONYY. | 5 | 5-7 | MONYY7.; | APRIL2020 | mmmyy or mmmyyyy mmm is the first three letters of the month name. yy or yyyy is a 2/4 digit year |

WEEKDATE. | 29 | 3-37 | WEEKDATE15. | Fri, Apr 24, 20 | day-of-week, month-name dd, yy (or yyyy) dd is day of the month(Integer). yy or yyyy is 2/4 digit year |

WEEKU. | 11 | 3-1200 | WEEKU3. | W16 | Week number in decimal format by using the U algorithm. *❷ |

WEEKV. | 11 | 3-1200 | WEEKV6. | 20W17 | Week number in decimal format by using the V algorithm. *❸ |

WEEKW. | 11 | 3-200 | WEEKW. | 2020-W16-05 | Week number in decimal format by using the W algorithm. *❹ |

WEEKDAY. | 1 | 1-32 | WEEKDAY. | 6 | date value as the day of the week (where 1=Sunday, 2=Monday, and so on). |

WEEKDATX. | 29 | 3-37 | WEEKDATX20. | Fri, 24 Apr 2020 | day of the week in the form day-of-week, dd month-name yy (or yyyy). dd is day of the month(Integer). yy or yyyy is 2/4 digit year |

WORDDATE. | 18 | 3-32 | WORDATE. | April 24,2020 | month-name dd, yyyy. |

WORDDATX. | 29 | 3-37 | WORDDATX. | 24 April,2020 | dd month-name yyyy. |

YEAR. | 4 | 2-32 | YEAR2. | 20 | Date values as the year. |

YYMM. | 7 | 5-32 | YYMM5. | 20M04 | yyMmm yy is 2/4 digit year M is the character separator to indicate that the number of the month follows. mm is an integer that represents the month. |

YYMMX. | 7 | 5-32 | YYMMS. | 2020/04 | yymm or yy-mm. yy 2/4 digit year. x is a specified separator. mm is integer month. |

YYMMDD. | 8 | 2-10 | YYMMDD6. | 200424 | yyMmm M is a character separator to indicate that the month number follows the M year as 2 or 4 digits. |

YYMMDDX. | 8 | 2-10 | YYMMDDB. | 20 04 24 | yymmdd or yy-mm-dd x – seperator |

YYMON. | 7 | 5-32 | YYMON5. | 24 APR | yymmm or yyyymmm. yy is 2/4 digit year. mmm is the name of the month (3 Characters) |

YYQ. | 6 | 4-32 | YYQ4. | 07Q1 | yyQq yy is 2/4 digit year Q is an integer (1,2,3, or 4) that represents the quarter of the year. |

YYQX. | 6 | 4-32 | YYQS32. | 2020/2 | yyq or yy-q yy is 2/4 digit year x is a specified separator. Q is an integer (1,2,3, or 4) that represents the quarter of the year. |

YYQR. | 8 | 6-32 | YYQRS8. | 2020/II | yyQqr qr is a roman numeral (I, II, III, or IV) that represents the quarter of the year. |

YYQRX. | 8 | 6-32 | YYQRC6. | 20:II | yyqr or yy-q yy is 2/4 digit 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 weeks within the year. Sunday is the first day of the week. The number of week values 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 weeks 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.