The Essential Guide to SAS Dates and Times, Second Edition (2014)

Appendix C. Troubleshooting Dates 101

This appendix is intended to be a quick solutions guide for some of the most common issues people have with dates and SAS. I hope to keep the discussion going online, because people often remark to me how difficult dates are in SAS, and they shouldn't be. You can find out more about this troubleshooting project on the author page for this book. Again, these are intended to be simple solutions to common beginner's questions.

Every person experiencing a problem with dates, times, or datetimes should immediately check to see if they are working with character values or numeric values. You can do this by using PROC CONTENTS, by looking at the properties in the Explorer window in interactive SAS, or by using the Data Set Attributes Task (under Tasks → Data) in SAS Enterprise Guide. Unless you are working with ISO 8601 durations and intervals, you should be working with numeric values. If not, then the first step you should take is to convert that character value into a proper SAS date, time, or datetime value.

Question 1: How do I convert my character value into a SAS date, time, or datetime value?

Case 1: If you are reading a flat file (CSV, tab-delimited, and so on), you will need to use the INPUT statement with the appropriate date, time, or datetime informat.

Sample Code

OPTIONS DATESTYLE=MDY;

DATA convert_char;

INFILE 'char_dates.txt' PAD MISSOVER;

INPUT id $ date1 :mmddyy10. date2 :date9. date3 :anydtdte.;

RUN;

Data File 'char_dates.txt'

101 04/17/2012 06dec2015 06272014

102 09/29/2014 15JUN2013 09feb2014

103 11/12/2013 3mar2015 08/19/2015

When you run the program with the data above, you will get the following data set. The date variables have intentionally been left unformatted so you can see that the data in the flat file has been turned into SAS date values. The ANYDTDTE. informat was used to read in the DATE3 variable to demonstrate how it can be used if you don't know what your character dates look like, or if they are in different date forms. Chapter 3 contains detailed descriptions of the informats that you can use in conjunction with the INPUT statement.

The Resulting Data Set

Case 2: If you already have your data in a SAS data set, then you will have to use the INPUT function to translate your character dates into SAS dates.

Here is the data set we have with character dates:

Code to Convert Character Date Values into SAS Date Values

1. OPTIONS DATESTYLE=MDY;

2. DATA fix_char;

3. set already_char;

4. num_date1 = INPUT(date1,mmddyy10.);

5. num_date2 = INPUT(date2,date9.);

6. num_date3 = INPUT(date3,anydtdte11.);

7. RUN;

The INPUT function is used in lines 4, 5, and 6 to create the new variables num_date1, num_date2, and num_date3. The reason we are creating new variables is that you cannot change an existing variable from character to numeric, so you cannot use the variable names date1, date2, or date3 because they exist in the data set ALREADY_CHAR. The INPUT function uses the informats detailed in Chapter 3, just like the INPUT statement.

The Resulting Data Set

In general, when you are dealing with SAS dates, times, and datetimes, you should be working with numeric variables. The exception to this rule is ISO durations and intervals, which is covered in Chapter 4.

Question 2: Why do I get the log message "Variable xxxxxx has been defined as both character and numeric"?

You are trying to convert a character variable into a SAS date, but you're using the same variable name in the INPUT function and for the result.

date = INPUT(date,mmddyy10.);

Instead, you must use a different variable name to store the result of the INPUT function.

new_date = INPUT(date,mmddyy10.);

Question 3: Why do I get the log message "NOTE: Invalid argument to function INPUT" when I try to use the INPUT function to convert a character date into a SAS date?

You are using the wrong informat for the characters you are converting. As an example, if your character values look like "04MAY2015," you'll get this error if you don’t use the DATE. (or ANYDTDTE.) informat.

Quickest fix: Use one of the "ANYDATE" informats. Read Section 3.4.4 for details and the possible pitfalls of this strategy.

Best fix: Find the correct informat that fits the character string you're converting.

Question 4: Why do I get errors when I am reading a flat file with dates, and I know I'm using the correct informat?

This is most often an issue of not reading enough characters in the field, or of reading too many.

Best fix: Make sure you specify the correct length on your informat, and use the colon (:) modifier for your informat (see Example 3.2).

Question 5: Why doesn't my date comparison work?

This is generally a problem when you are importing data from another database. First, ensure that the variables you are working with are numeric. Second, many databases store their dates as datetime values, so even if using dates works in the database, it will not work when you try to compare that with a SAS date value.

Best fix: Once you've verified that your data aren't character, try using the DATEPART() function (Table 5.3) on the variable you've imported and compare that with your SAS date.

Question 6: My date values look right, so why can't get I my date comparison to work?

This frequently means that one of your values is character. Removing formats from a SAS date value is a good way to make sure that you've got a SAS date value. As an example, if the date is in 2014, it should be a number between 19724 and 20088.

Question 7: How can I subset my data before or after a specific date/time/datetime?

You have to use a date literal such as '01JAN2014'd, a time literal such as '14:00't, or a datetime literal such as '17OCT2014:07:00'dt.

DATA junestock;

SET sashelp.citiday;

WHERE date BETWEEN '01JUN1988'd AND '30JUN1988'd;

RUN;

This is one way to create a subset of data for June 1998 from the data set SASHELP.CITIDAY. However, don't let the format of the date fool you. Here's the same method applied to the data set SASHELP.CITIMON.

DATA stock8081;

SET sashelp.citimon;

WHERE date BETWEEN '01JAN1980'd AND '31DEC1981'd;

RUN;

Even though the dates are monthly, they are still SAS dates, so you have to specify the boundaries as complete SAS dates, not just a month and year.

Question 8: How do I get a date from a date and time?

Use the DATEPART() function after you've converted it to a SAS datetime value. If you just need the time, use the TIMEPART() function. This example takes a datetime value and separates it into date and time, formatted and unformatted versions.

DATA date_from_datetime;

datetime = '8APR2014:16:00'dt;

fmt_datetime = datetime;

date = DATEPART(datetime);

fmt_date = date;

time = TIMEPART(datetime);

fmt_time = time;

FORMAT fmt_datetime mdyampm21. fmt_date mmddyy10. fmt_time timeampm.;

RUN;

PROC PRINT DATA=date_from_datetime NOOBS;

RUN;

Question 9: Why do my dates look like a bunch of numbers that don't make any sense, and how can I fix it?

If your date value looks like a number such as 16789, then it is displaying as its SAS date, and all you need to do is to format it using one of the date formats in Appendix A, or if you need one of the NLS formats to translate the date into a language other than English, see Section 6.4 andAppendix B.

Question 9a: I formatted my date and now all I get is a bunch of asterisks (************). What's wrong?

If your unformatted SAS value is more than 7 digits long, or you've used a date format and you get asterisks, you probably have a datetime value, and you need to use a datetime format. See Appendix A for the quick list.

Question 10: My date looks like "06/25/2014," but I need to make it look like "2014/06/25." I know it's a SAS date value in the data set, so how do I change it?

Use the FORMAT statement. It does not matter what format the variable has associated with it in the data set. As long as it is a SAS date, time, or datetime value, you can change the display by changing the format in the procedure where you are displaying the variable. However, if you get an error message along the lines of "Format $... not found," the dollar sign ($) is telling you that the variable you are trying to format is a character variable. In that case you will have to convert it to the corresponding SAS value.

Let's print out some records from a data set using this code:

ODS RTF FILE="apxc_10.rtf";

PROC PRINT DATA=book.dailysales (OBS=5);

VAR date;

RUN;

What you see in the date column is just a SAS date that has been associated with the DATE9. format when the data set was created, so without any FORMAT statement in the PRINT procedure, that is what it looks like. Now we'll add a FORMAT statement.

1 PROC PRINT DATA=book.dailysales (OBS=5);

2 VAR date;

3 FORMAT date yymmdds10.;

4 RUN;

And that's how you change the way a date, time, or datetime looks in output from its default format.

Question 11: My date is numeric, like 20140815. How do I get it to display as 08/15/2014?

This is usually the result of an import that went wrong. First, make sure that it really is the number 20,140,815. If this is true, then you can turn that number into a character string with the PUT function and convert it to a date value like you do with any other character date string. Here's one way:

DATA c11;

bad_date = 20140815;

char_date = PUT(bad_date,10.);

SAS_date = INPUT(STRIP(char_date),yymmdd8.);

fmt_SAS_date = SAS_date;

FORMAT fmt_SAS_date weekdate.;

RUN;

PROC PRINT DATA=c11 NOOBS;

RUN;

Question 12: How can I display just the month and year, when I have month, day, and year?

By definition, SAS dates are a specific day in a specific month during a specific year, so you can't just delete the day and store the month and year. If you want to display the value as only a month and a year, then just use a format that only displays the month and year.

Question 13: How do I convert from datetime format to date format?

In order to answer this question, you need to know what you want. If you need a SAS date value, then you need to convert it from seconds since midnight, January 1, 1960, to days since January 1, 1960, by using the DATEPART function. If you just want it to display like a date, and don't need or want the extra variable in your data set, then you can use a format. See Appendix A for the available SAS datetime formats and the output they produce.

DATA q12;

datetime = '31AUG2014:10:15'dt;

fmt_datetime = datetime;

date_from_datetime = DATEPART(datetime);

fmt_date = date_from_datetime;

FORMAT fmt_datetime dtwkdatx. fmt_date weekdatx.;

RUN;

PROC PRINT DATA=q12 NOOBS;

RUN;

Note the difference in the actual SAS values. Even though the formatted values are identical, the variable datetime is a SAS datetime value, while the variable date_from_datetime is a SAS date value created with the DATEPART function.

Question 14: I can't find a SAS format to make my date/time/datetime look the way I want. OR: I need to output my date so it looks like …

First, check Appendix A and the SAS documentation to see if you can find a format that matches your needs. If not, then use the FORMAT procedure with the PICTURE statement. Section 2.7 of the book provides all the details you need to create a custom format, including the SAS date directives that tell SAS what you want your date, time, or datetime to look like. Don't worry that you may be duplicating an existing SAS format; that will not cause a problem as long as you do not use the same name as a SAS-supplied format. You will see a note in the log, and your custom format will not be created.

Question 15: How can I read a date/time/datetime formatted like …?

The simple answer is to use one of the "ANYDATE" informats. Don't forget about the DATESTYLE= system option, and check your results carefully before using them. The "ANYDATE" informats are easy, but they can be fooled, even when four-digit years are used. See Section 3.4.4for details.

Question 16: How can I convert a UTC time value to a specific time zone?

If your "time value" is a datetime, use the TZONEU2S() function. This changes the SAS value, so don't overwrite your original value unless this is what you want to do. If you have just a time value, you will need to get the offset via the TZONEOFF() function, which will return the offset from GMT for the time zone you supply.

DATA q16;

datetime = "26MAR2014:11:47:00"dt;

gmt_datetime = TZONES2U(datetime);

RUN;

PROC PRINT DATA=q16 NOOBS;

FORMAT datetime gmt_datetime mdyampm21.;

RUN;

Question 17: How can I make a single date variable from separate month, day, and year?

The MDY function will let you supply a numeric month, day, and year, and it will calculate the SAS date value. You see this often when reading Excel files, where the month, day, and year are in separate columns. We'll use in-stream comma-separated data instead of an Excel file for the example.

1 DATA Q17;

2 INFILE DATALINES DLM=',';

3 INPUT month day year;

4 SAS_date = MDY(month,day,year);

5 fmt_date = SAS_date;

6 FORMAT fmt_date mmddyyd10.;

7 DATALINES;

8 2,15,2013

9 9,6,2014

10 12,17,2013

11 5,22,2014

12 ;;;;

13 RUN;

14 PROC PRINT DATA=q17 NOOBS;

15 RUN;

The SAS_date column shows the SAS date value resulting from the use of the MDY function in line 4.

Question 18: I have a date and a time. How can I make a SAS datetime from them?

This occurs when you import data into SAS and the date is in one column and the time is in another. The DHMS() function is what is used to create datetimes from dates and times. Although you might think that you have to break the time value into hours, minutes, and seconds to use the function, remember that SAS times are maintained in seconds since midnight, so you can leave hours and minutes set to zero and put the time variable into the seconds parameter. We will use in-stream data for the example. Pay attention to line 3. This is how you create a datetime from a SAS date and a SAS time.

1 DATA q18;

2 INPUT date : yymmdd10. time : time.;

3 datetime = DHMS(date,0,0,time);

4 fmt_date = date;

5 fmt_time = time;

6 fmt_datetime = datetime;

7 FORMAT fmt_date mmddyy10. fmt_time timeampm. fmt_datetime
mdyampm21.;

8 DATALINES;

9 2014-8-17 5:45

10 2014-03-11 9:06

11 2015-02-27 15:43

12 2014-12-13 10:00

13 ;

14 RUN;

15 ODS RTF FILE='apxc_18.rtf';

16 PROC PRINT DATA=q18 NOOBS;

17 RUN;

date and time are the SAS values as read from the data, and datetime is the SAS value created in line 3 of the above code. We have created duplicate variables for formatting.

Question 19: How can I calculate the number of days between two dates?

There are multiple ways to calculate the difference between two dates, times, or datetimes in SAS. This is one of the best things about having them represented as numbers. The simplest way to find the difference is to subtract one date from another. It is important that you make sure that you are subtracting dates from dates, times from times, and datetimes from datetimes.

Question 20: How do I convert a numeric SAS date value into character format?

The first question you should ask yourself is why. Formatting the SAS date value will give you the display you want. Even when you are exporting to other software, as long as you put the date into a form that the other software understands, it should work fine. You don't need to do the character conversion, and you will not be able to do any calculations with that character variable. The only time that I would convert a date value into a character value is when I need to put the date inside of a long string of text such as, "The subject started the trial on January 15, 2012, and mastered task A within 15 minutes." It doesn't matter if a format is permanently associated with the date variable or what it looks like when you look at the data set; remember that you can change the way a date is displayed in any procedure where you can use a FORMAT statement.

Here's a data set with a variable that has been formatted with the WORDDATX. format. This means that anytime the variable DATE is displayed in a SAS procedure, it will be displayed with the WORDDATX. format as shown below.

Now, let's run a PROC PRINT, but I want to show the date as DD-MMM-YYYY, so I use the FORMAT statement to change the display for this run:

PROC PRINT DATA=q20 NOOBS;

FORMAT date date10.;

RUN;

Formatted with DATE10.

What if you want to see it as YYYY-MM-DD?

PROC PRINT DATA=q20 NOOBS;

FORMAT date yymmddd10.;

RUN;

Formatted with YYMMDDD10. (forces the separator to be the dash)

But when we remove all the formats by using a FORMAT statement without a format name, we see the actual SAS date value:

PROC PRINT DATA=q20 NOOBS;

FORMAT date;

RUN;

Format Removed

If you still want to convert your SAS date into a character value, then you can use the PUT function. Line 2 is a precaution to make sure that the character variable is long enough to hold the longest value. Note that you have to use a different name for the character variable you create in line 4.

1. DATA q20_plus;

2. LENGTH chardate $ 31;

3. SET q20;

4. chardate = PUT(date,weekdate.);

5. RUN;