Tips and Tricks for Working with Dates and Times in SAS Q&A, Slides, and On-Demand Recording

[#1]

Watch this Ask the Expert session to learn tips and tricks to effectively work with SAS dates, times, and datetime values using SAS informats, formats, functions, date/time constants, and date directives. 

Watch the webinar

You will learn how:

• SAS stores date, time and datetime values.
• SAS informats can be used to read common date and time expressions.
• Date and time formats can specify how the values are displayed.
• SAS functions can be used to extract information from dates, times and datetime values.
• Using directives can help specify templates for date, time and datetime values.

The questions from the Q&A segment held at the end of the webinar are listed below. The slides and code from the webinar are attached.

Q&A

Can you do 30 days before (-30)? 

Yes, when using the INTNX function, you can use a negative number. For example, if you wanted to go back 30 days from today’s date you could use the following assignment statement to go back 30 days, or if it was months, you could use a negative number to go back X number of months, years, etc.  

_30DaysAgo=intnx('DAYS',today(),-30);  

Feel free to look at the INTNX function documentation. 

Do you have any tips for converting between SAS dates and UNIX dates? This is something I have to work with if a SAS date has been loaded to UNIX as an integer rather than a date type. 

As you are aware, the starting point for SAS Dates and UNIX dates are different. SAS start date is 01Jan1960 and UNIX. There are several online resources you can look at to assist with this conversion:  

Converting a UNIX Datetime Value to a SAS Datetime Value  

Blog- How to convert a Unix datetime to a SAS datetime 

Are there limits on the earliest dates and latest dates that SAS can handle?   For example, how far back in history can I go and how far into the future? 

SAS can perform calculations on dates ranging from A.D. November 1582 to A.D. 19,900. So, SAS Dates will live on well past our time here on Earth! 

I work with Excel files that have dates. How can I work with these in SAS after I import that data? 

Is your tutorial going to be the same for SAS EG users? 

SAS Dates and Excel dates have a different internal representation. But the one good thing is IF your column that contains the date in Excel is formatted as a Date, when you import the data into SAS, it ‘should’ read the data correctly as a SAS Date value. But there have been several community posts and papers written about this issue, so look at the various discussions and solutions if formatting the column in Excel as a date doesn’t fix the issue.  

The SAS engine that reads the data will interpret the data correctly and read it in as a SAS Date by selecting the appropriate reading instruction when it’s imported, but in cases where it doesn’t interpret the data correctly, you can get some tips from the following resource:  

Dating SAS® and MS Excel (Which is also mentioned in the link above) 

Is your tutorial going to be the same for SAS EG users?  

Yes, SAS Date, SAS Time and SAS Datetime values will be the same regardless of your SAS programming environment; Enterprise Guide, SAS Studio or SAS Windowing environment. 

When I import an excel file, the column gets populated with the underlying date serial number of Excel rather than the SAS Date or actual Date format in Excel. What's the difference between underlying date serial number in Excel and SAS Date? How do I convert the Excel underlying date serial number? 

Here is an article that might help you with the SAS date to excel problem. 

I just have SAS Base 9.4. I don't believe that I get SAS Studio; so, I have to write code. Is there any way I can access that? 

In order to run SAS Studio, you need the second maintenance release for Base SAS 9.4 or later installed on your SAS server. SAS Studio submits your code to your SAS server, which means that you can use any products that are installed on your SAS server. I would suggest you start internally with someone at your company and ask a SAS administrator or someone who oversees your SAS license. You may find you have access to SAS Studio, and you just need to talk to someone to get access to it. You should also check out the SAS Studio website here and request a demo, select the link to contact us to get more information on installation and pricing 

Should we sort date in SAS date value or character SAS date format? 

If you try to store values as character dates and sort them, you can’t always ensure they sort correctly. For example, a character form of a date as 01APR2022 will always show up before 01JAN2022 because “A” comes before “J” in the sorted sequence of character values.  

So don’t see the need to sort dates by their character form. Dates should be represented as their internal numeric value represented by the number of days since January 1, 1960, then when you sort them, they will always sort chronologically by their internal date value. Also note, that the SAS date formats just alter the way the values are displayed so they are recognizable to the human eye, it doesn’t change the internal stored value within the data set.  

There was a question answered about this in one of our SAS Community posts:  

Sorting data by dates in character form 

Do we have a function to convert time zones? For instance, I'm in BRT and I want to change to CET. 

Conversion from one time zone to another is always complex, especially when you factor in the time difference between different countries. And there really is no easy way to do it. Look at this SAS Global Forum paper to read more:  

https://www.sas.com/content/dam/SAS/support/en/sas-global-forumproceedings/2020/4742-2020.pdf 

Is there anywhere to get a good "cheat sheet" for INFORMAT values?  Like something that would quickly show us what each INFORMAT value needs to be for various/popular formats? For instance, I can never remember that MMDDYY10 is for MM/DD/YYYY. 

A cheat sheet would be a great idea. I have not seen one, so the closest you get to it are the Informats by Category or the Formats by Category documentation pages. 

When converting character dates/times to numeric, can you use the anydtdte INFORMAT instead of having to match the INFORMAT with how the character version of the date/time variable is written? 

Yes, you can. 

When you change the format of date and time, is there a way to have it shown in SAS 360 and use a between? 

I’m not exactly sure I understand the question. If you are asking if you want to use the BETWEEN AND Operator for SAS dates and you wanted to specify the SAS internal representation for the date as the integer, the answer is yes, you could just use a number. The challenge becomes in trying to determine what that internal number is. Someone pointed out a SAS Date converter below. That may be an easy way to determine the integer, but it will require a two-step process. 1) Determining the actual SAS Date value and 2) next copying and pasting the value. It is a more simplistic process to just use the Date constant and let SAS determine what that integer value is. 

We use SAS EG. I see this is in SAS Studio. Do we need to adjust for SAS EG? 

No, the SAS code would be the same. SAS Studio like Enterprise Guide is an interface you can use to write your SAS code. If you are interested in seeing the difference between SAS Studio and Enterprise Guide, please look at the SAS Studio: Help Center FAQ for SAS Studio vs. Enterprise Guide. 

What technique are you using to comment and uncomment lines without having to type /*   */? 

In SAS Studio to comment and uncomment code you use the CONTROL key + / (forward slash). This works as a toggle to comment and uncomment.  

In SAS Enterprise Guide and SAS Windowing Environment you use CONTROL key + / (forward slash) to comment. CONTROL key + SHIFT key + / to uncomment. 

Do you have any tips for using macro variables to hold dates? 

When you talk about macro variables, macro variables are always character strings. How SAS will interpret them and resolve them, so it uses them as a SAS date is different.  

If you need to use them as an actual SAS date, then you’ll need to write SAS code that will convert the macro variables to an actual SAS Date value. We discuss this in detail in our SAS® Macro Language 1: Essentials course.  

Here is an example that I’m using to illustrate. However, a better way to retrieve values from a macro variable when you are using a DATA step. If you are interested, look at the %SYMGET macro function.  

%let mvar_Date=19Oct2022;  

data sasdate;  

sasdate="&mvar_Date"d;  

run; 

What does the HMS function do? I've seen DHMS code in a lot of code I have read, but I don't fully understand its use. 

HMS stands for Hour, Minute, Second function. It allows you to provide three arguments, hour, minute and seconds and the function returns a SAS time value from hour, minute, and second values. You read more about the HMS function here. 

Why does SAS use Jan 1, 1960, as the reference? 

The following publication states: https://support.sas.com/publishing/pubcat/chaps/59411.pdf the starting date has to do with approximate birth date of the IBM 370 system. 

Sometimes when I generate a report in SAS, the time is wrong. How do I correct that?  Do I always have to close SAS out for it to update the new time when I run a report? 

The time that you are seeing in your report is the time that your SAS session started, so when you submit your code to generate your reports, the date and time is not always accurate with actual time and sometimes the actual date if you leave your SAS Session open overnight, it will show the previous day’s date on the report that was generated. You can turn off the date and time and write a custom TITLE or FOOTNOTE that will always display the current date or time.  

We discuss this code in detail in our SAS® Macro Language 1: Essentials course.  

/* Turn off default date/time stamp */  

options nodate;  

title "Report generated on %sysfunc(today(), mmddyy10.) at  

%sysfunc(time(),timeampm.)";  

proc print data=sashelp.cars(obs=5);  

var make model type;  

run; 

Is there any way to change the default SAS date format without having to reformat the date every time? Is there a system option that could be altered? 

The default display of a SAS date is the internal numeric value representing the number of days since January 1, 1960, and the actual date. If you wish to provide a FORMAT associated with the variable in the table when the table is created, that will be used to display the date instead of the internal representation. The beauty of SAS FORMATS is that they are flexible and allow users to decide how they want the values displayed, so you aren’t limited to one way to show them. 

When should I use INPUT vs INFORMAT? 

The INPUT statement is a SAS statement that describes the arrangement of values and how to read data from in external raw data file or flat file and assigns input values to correspond to SAS variables. INFORMATS are typically used to read or input data from external files and instruct SAS on who to read data into SAS variables. In the case of SAS date Informats, they tell SAS how to convert the raw form of the date in the raw data/external file into a SAS Date.  

To learn more about SAS Informats, look at this Introduction to SAS Informats and Formats document. 

Is there a SAS provided format that will display DD/MM/YYYY HH:MM?   (Without using a user written format) 

No, there isn't a SAS format that will display DD/MM/YYY HH:MM, so you would have to create a user-defined or user-written FORMAT with the FORMAT procedure. There are just so many different variations to display dates, times and datetime values, so it's nice to be able to create your own FORMAT in SAS. 

Are SAS Directives already set in SAS or are these created by the SAS programmer? 

The actual SAS Directives are set by SAS, you can’t create your own directives, but you can use the SAS Directives that are available from SAS so you can decide exactly how you want your values displayed. SAS couldn’t possibly think of the different variations, so it’s nice to have the ability and flexibility to create your own. 

Is "Mydate" a user-defined format or the SAS format? 

Yes, MYFORMAT was a user-defined format I created the demo3.sas in the PROC FORMAT step. I used SAS directives to create it. 

Are the SAS directives shown for use in picture format only? Can we use them elsewhere? 

SAS directives are for use with the PROC FORMAT picture statement only. It can’t be used anywhere else with PROC FORMAT. 

Could you talk about the meaning of SAS options below: OPTIONS YEARCUTOFF=1950; 

My recommendation when working with SAS dates is to always use a 4-digit value for the year. Then there is no question regarding whether a date of 01/01/00 is January 1, 1900 or January 1, 2000? The SAS YEARCUTOFF= option determines how years with only 2 digits are handled. This option specifies the first year of a 100-year span that is used by date informats and functions to read a two-digit year. Please look at our documentation for more information here. In short with a value of 1950 for YEARCUTOFF, any two-digit value for a year in a 100-year span from 1950 to 2049 is going to be a year of 1950 to 2049. A value of 50 would not be 2050, unless you specify a 4-digit value for year.  

Here are two examples with different values for YEARCUTOFF=.  

options yearcutoff=1950;  

data yearcutoff1;  

date1="01Jan50"d;  

date2="01Jan51"d;  

date3="01Jan00"d;  

date4="01Jan40"d;  

date5="01Jan49"d;  

date6="01Jan09"d;  

date7="01Jan99"d;  

date8="01Jan2050"d;  

format date1-date8 mmddyy10.;  

run;  

title "Yearcutoff=1950";  

proc print data=yearcutoff1;  

run;  

options Yearcutoff=1900;  

data yearcutoff2;  

date1="01Jan50"d;  

date2="01Jan51"d;  

date3="01Jan00"d;  

date4="01Jan40"d;  

date5="01Jan49"d;  

date6="01Jan09"d;  

date7="01Jan99"d;  

date8="01Jan2050"d;  

format date1-date8 mmddyy10.;  

run;  

title "Yearcutoff=1900";  

proc print data=yearcutoff2;  

run;  

*Note: 4-digit values for the year will always read the century portion of the year accurately. 

Here is an online SAS date converter: https://www.sastipsbyhal.com/2012/01/sas-date-calculator-now-available.html 

Are there any resources for imputing dates? 

You may be interested in the following Blog post from one of our SAS Communities: Date Imputation for Partial Dates. 

Regarding creating our won format, could we create our own INFORMAT to read in data? 

Yes, you can create your own SAS user-defined informats. Instead of a VALUE statement or a PICTURE statement, you would use the INVALUE statement in your PROC FORMAT code to create a user-defined INFORMAT. 

Is there a trick for when I want to compare the date from two datasets to see if they match? Many times, I see what looks like the same date, but they don't seem to compare as a match. IF DATE1 = DATE 2 then.... 

Unfortunately, without seeing the actual data, it’s hard to say. There are lots of variations I could think of. Like for the two data sets, do the rows match exactly row for row with the dates you wish to compare? If so, you could do a blind DATA step MERGE to combine the two tables and then compare DATE1 and DATE2, but what if the date columns have the same column name? Then you would have to use the RENAME data set option to give them different names so the columns would not be overwritten when you merge the two tables. Is there an identifier that can be used to match merge the rows from the two tables to make a comparison? Basically, if you combine two tables and there are two dates, you in theory should be able to compare one date to the other, but again, without knowing the data, I’m just making a guess about suggestions. Looking at the data, one could possibly use a different approach. 

Why not use the "any" date and time format (besides pointing out errors) 

It’s always best to know your data. I know this isn’t always possible. As with all dates and all date INFORMATS, you may find that some dates still aren’t read in correctly. And while anydate is more flexible, if there happen to be values that anydate can’t read, you may end up with missing values and must come up with additional ways to handle the dates if they aren’t read in correctly. 

Do you have recommendations for dates that are read in as character and ensuring that converting numeric retains the correct value? 

You must know your data such that you select the correct data INFORMAT to read the data correctly when you use the INPUT function to convert it from its character for to the SAS Date numeric form. 

When you export your dates to MS Excel, I have noticed that sometimes it keeps its format but sometimes the format is dropped. Why? 

Without seeing the data from the Excel spreadsheet, I can only guess what is going on. Sometimes the dates are not formatted as dates in the Excel spreadsheet, and sometimes some of the values are formatted as dates and some the columns are mixed (formatted and not formatted as dates). If the column has mixed formatting for the date values, SAS makes the whole column a character form of the date when it converts it to SAS and the format is dropped. Look at this blog post: Excel to SAS date format issues. 

How do you clean up dates in a column where some are entered in Excel as dates and some as text? 

It would probably be best to read the date field in as a character column in one step and then in another step, convert the character column to a SAS numeric date column using the INPUT function. 

What informat would you use to convert UK dates to SAS dates? 

Conversion to UK dates or any time zone is going to be a challenge and require a bit more coding. Here is a SAS Global Forum paper that might be helpful, but there may be code you may not completely understand and adjustments that still need to be made for your specific conversion.  

Any Time Zone to Any Time Zone: A Macro to Convert Anything 

How do you know when to use the d ... order date = "01Mar2022"d;? 

How and when you use a SAS date constant is up to you. You could use it to create a variable that contains as SAS date values with an assignment statement like the one above. You could use it if you were trying to filter a table based on a SAS date column. When you need to know the SAS date value, you use a Date constant to determine that SAS date value.  

Here is an example of using a Date constant to filter one of the SASHELP tables with PROC PRINT to return rows with dates between two SAS date values of interest. Since I don’t know the exact SAS Date value for the two dates of interest, I used the SAS date constant to make the determination.  

Example:  

proc print data=sashelp.citiday;  

where date between "01May1990"d and "31May1990"d;  

run; 

What is your favorite formula to calculate exact age? 

There is often debate about the best way to create an exact age in SAS. So, the answer to this is it depends on the goal, and one must know that depending on the calculation and functions you use, you could get slightly different results, because there’s always more than one way to do computations in SAS. Feel free to check out the following resources: 

https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/060-30.pdf 

Community Blog Post: Calculate age 

Anydate INFORMAT works differently in different regions: 5/3/2022 would be March 5 in Europe, but May 3, 2022. How does SAS know that 5/3/2002 is March 5th instead of May 5th or vice versa?  

There is a DATESTYLE= System Option that determines how SAS interprets 5/3/2022. If the DATESTYLE= option is set to MDY (month, day, year), then 5/3/2022 is read in as May 5, 2022 when the ANYDATE Informat is used. If the DATESTYLE= option is set to DMY, then the date is read in as March 3, 2022.  

You can submit the following code to see how what the value is for your SAS system option for DATESTYLE=, the results of the code below will show up in your SAS log.  

proc options option=(datestyle);  

run; 

Do all these functions work with SQL? 

Yes, all the SAS functions I have mentioned in this Ask the Expert work with the SQL Procedure. PROC SQL supports many of the functions available in the SAS DATA step. Some of the functions that are not supported are the variable information functions and functions that work with arrays. 

Are there any dates SAS can’t read? 

I haven’t encountered a date that SAS can’t read. Here is a blog you might find interesting that discusses this very question: One informat to rule them all: Read any date into SAS. 

Sometimes my dates have a period displayed, why is that?  

There are some SAS Date FORMATS that allow you to display periods as part of the date value for example instead of displaying a date as 10/19/2022 (MMDDYY10.), the date can be displayed with periods, 10.19.2022 (MMDDYYP10.), or it can display the date with blanks, 10 19 2022 (MMDDYYB10.). 

You can see a list of FORMATS that allow other characters to separate the date values here. To view the descriptor of a SAS data set to see the FORMAT associated with the variable, you can use the CONTENTS procedure. 

Recommended Resources

SAS® Programming 1: Essentials

SAS® Programming 2: Data Manipulation Techniques

About SAS Date, Time, and Datetime Values Documentation

SAS Tutorial | SAS Date Functions FAQs

Please see additional resources in the attached slide deck.

Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.  

[DaveAus]

Greetings All

I need to convert a SAS EG date format to EXCEL date format because the particular report will be distributed as an EXCEL file.

The SAS code displays the date as 09MAY2023 (DATE9), whereas the EXCEL file has to display the same date as 9-MAY-23 (d-mmm-yy).

The SAS EG Query Builder Modify Task dialog box allows changing format, but the needed EXCEL date format doesn't appear in the drop-down list of formats.

There is an 'Expression:' field in the 'Properties' dialog box - Is there a conversion expression I can use to convert the date?

[Kevin_Leonte]

@DaveAus Since SAS doesn't have a format that is exactly the d-mmm-yy format (the closest is date11. or dd-mon-yyyy) then you will need to create a picture format:

proc format ;

     picture date9d other = '%0d-%b-%0y'( datatype= date ) ;

run;

Once you run this proc format the format date9d. would be available under the user defined formats in the EG Query Builder.

Or you can modify the SQL code that the Query Builder generates and just add this proc format at the top and then use the format in the sql select statement for your date field(s)

i.e. (SELECT t1.DATA_AS_OF_DATE FORMAT=DATE9D. AS DATA_AS_OF_DATE)

[DaveAus]

Thanks Kevin, for your solution, much appreciated 😊

[Kevin_Leonte]

@DaveAus  You're welcome!