How to Easily Convert a String into a Date in SAS

In this article, we discuss how to convert a text string into a SAS date.

In short, you convert a date-looking text string into a real date with the INPUT function. This function reads a string and returns the numeric representation of a specific date in the SAS language. The INPUT function requires two arguments, namely a text string and a date format.

Besides converting a text string into a SAS date, we show how to create a datetime variable from a string. We also demonstrate how to use the INPUT function in PROC SQL and how to convert a macro variable into a SAS date.

If you have a number that looks like a date (e.g., 01012020), then we recommend reading this article.

What is a Date in SAS?

A SAS date represents the number of days between January 1, 1960, and a specific date. Hence, a date in SAS is a numeric variable, not a character string. Therefore, to carry out operations with dates, you first need to make sure that they are saved as a numeric variable.

Since SAS stores a date as a number, it is difficult for humans to make sense of it. For example, the number 22.280 represents December 31, 2020. Therefore, SAS uses formats to display these numbers as dates and make them understandable for the human eye.

data work.ds;
    input date_number;
    date_format = date_number;
    format date_format date9.;
    datalines;
-1
0
1
2
22280
;
run;

Although you won’t easily reach them, SAS dates have their limits. You can only work with dates in SAS between January 1, 1582, and December 31, 19900.

How to Convert a String into a Date?

Even though a text string might look like a date, SAS doesn’t treat them as such. As a consequence, you can’t carry out operations with dates that are stored as strings. Hence, you first need to convert them into an actual date before you can work with them.

You can convert a text string into a SAS date with the INPUT function. The INPUT function reads a string that looks like a date, and given its format (e.g., mm-dd-yyyy or ddmmmyyyy), returns a number. This number represents a date in the SAS language.

INPUT(text_string, date_format);

The second argument of the INPUT function (i.e., date_format) is critical when you convert text into a date. This argument specifies how SAS interprets the text string of the first argument. For example, if your text string is 12/31/2020, then you need the MMDDYY10. format as the second argument.

Notice that, if you use an incorrect format, the INPUT function returns a missing value, or worse, returns a numeric value that doesn’t correspond to the date in the text string. For example, SAS could interpret 11/10/2020 as October 11, 2020, or November 10, 2020. So, be careful with the format you use.

The table below shows how to convert text strings of different formats into a SAS date.

Next, we provide some examples that use the SAS code above.

data work.ds;
   date_string = "31-12-2020";
   date_number = input(date_string, DDMMYY10.);
   output;
 
   date_string = "4APR2021";
   date_number = input(date_string, DATE9.);
   output;
 
   date_string = "2017-08-31";
   date_number = input(date_string, YYMMDD10.);
   output;
 
   date_string = "25/05/2021";
   date_number = input(date_string, DDMMYY10.);
   output;
 
   date_string = "06/28/2021";
   date_number = input(date_string, MMDDYY10.);
   output;
run;

Now that we have converted a string into a number, we can use the new column to carry out operations. For instance, finding the difference between two dates. However, the numbers in the new column aren’t easy to interpret. Therefore, we will use a format to make these values understandable for the human eye.

For example, in the code below we use the FORMAT statement and the DATE9. format.

data work.ds;
    date_string = "31-12-2020";
    date_number = input(date_string, DDMMYY10.);
    date_number_format = date_number;
    output;
 
    date_string = "4APR2021";
    date_number = input(date_string, DATE9.);
    date_number_format = date_number;
    output;
 
    date_string = "2017-08-31";
    date_number = input(date_string, YYMMDD10.);
    date_number_format = date_number;
    output;
 
    date_string = "25/05/2021";
    date_number = input(date_string, DDMMYY10.);
    date_number_format = date_number;
    output;
 
    date_string = "06/28/2021";
    date_number = input(date_string, MMDDYY10.);
    date_number_format = date_number;
    output;
 
    format date_number_format date9.;
run;

There exist numerous formats for SAS date variables. The table below shows the most popular ones.

How to Convert a String into a Datetime?

Similar to converting a text string into a date, you can also convert a text string into a datetime.

A SAS datetime variable in the number of seconds between midnight January 1, 1960, and a specific date including hour, minute, and second. For example, the number 1925078399 represents December 31, 2020, at 23:59:59.

You convert a string that looks like a datetime (e.g., 31DEC2020 23:59:59) into a SAS datetime variable with the INPUT function. This function requires two arguments, namely the text string and an informat. The informat specifies how SAS interprets the text string.

INPUT(text_string, datetime_format);

In contrast to the number of date formats, there don’t exist many datetime formats. Usually, one uses the DATETIME. format to read in a datetime text string. In the example below, we show how to use this format and convert a text string into a datetime variable.

data work.ds;
    length datetime_string $50;
 
    datetime_string = "31DEC2020 23:59:59";
    datetime_number = input(datetime_string, DATETIME.);
    output;
 
    datetime_string = "31DEC2020 11:59:59 AM";
    datetime_number = input(datetime_string, DATETIME30.);
    output;	
 
    datetime_string = "31DEC2020 11:59:59 PM";
    datetime_number = input(datetime_string, DATETIME30.);
    output;	
run;

Like a date variable, the number that represents a datetime variable isn’t interpretable for humans. Therefore, you can use the FORMAT statement to make sense of these numbers. For example, the DATETIME. format.

data work.ds;
    length datetime_string $50;
 
    datetime_string = "31DEC2020 23:59:59";
    datetime_number = input(datetime_string, DATETIME.);
    datetime_number_format = datetime_number;
    output;
 
    datetime_string = "31DEC2020 11:59:59 AM";
    datetime_number = input(datetime_string, DATETIME30.);
    datetime_number_format = datetime_number;
    output;	
 
    datetime_string = "31DEC2020 11:59:59 PM";
    datetime_number = input(datetime_string, DATETIME30.);
    datetime_number_format = datetime_number;
    output;
 
    format datetime_number_format datetime23.;
run;

If you already have a date variable, then you can use the DATETIME function to easily create a datetime variable. The opposite is also true, you can create a date variable from a datetime variable with the DATEPART function.

How to Convert a String into a Date in PROC SQL?

So far, we have demonstrated how to convert a string into a date(time) variable in a SAS Data Step. However, you can also do this with PROC SQL.

Converting a text string into a date(time) in PROC SQL is similar to the same operation in a SAS Data Step. You use the INPUT function followed by the string you want to convert and the informat of the date(time) variable. As a result, SAS returns the number that represents your date(time) variable.

The SAS code below shows how to convert a string into a date variable (both with and without format).

data work.ds;
    date_string = "31DEC2020";
    output;
 
    date_string = "03JUL2021";
    output;
 
    date_string = "10OCT2021";
    output;
run;
 
proc sql;
    select date_string,
        input(date_string, date9.) as date_number,
	input(date_string, date9.) as date_number_format format=ddmmyy10.
    from work.ds;
quit;

Instead of converting a text string into a SAS date, you can use PROC SQL also to convert a sting into a datetime variable. The example below shows how we use the INPUT function to read the text string.

data work.ds;
    datetime_string = "31DEC2020 23:59:59";
    output;
 
    datetime_string = "03JUL2021 17:30:00";
    output;
 
    datetime_string = "10OCT2021 04:15:30";
    output;
run;
 
proc sql;
    select datetime_string,
	input(datetime_string, datetime.) as datetime_number format = best20.,
	input(datetime_string, datetime.) as datetime_number_format format=dateampm.
    from work.ds;
quit;

How to Convert a Macro String into a Macro Date?

Until now, we have discussed how to convert a text string (either hardcoded text or a variable) into a date variable. However, you could have a macro variable that looks like a date.

In this section, we assume the following before we convert a macro variable into a date variable:

1. You have a macro variable that looks like a date (e.g., 31DEC2020), and
2. You want to create a new macro variable that contains the numeric representation of this date (e.g., 22280)

In this case, you convert a macro variable into a date with the SYSFUNC function and the INPUTN function. First, the SYSFUNC function lets you use SAS functions outside a SAS Data Step or PROC SQL. Then, with the INPUTN function you convert the date-looking macro variable into a real macro date.

The INPUTN function is similar to the INPUT function because they both convert text into numbers. However, you can use the INPUTN function in a %LET statement, whereas you can’t do this with the INPUT function.

In the example below, we first use the %LET statement to create a macro variable. Then, we use the SYSFUNC and INPUTN functions to convert this macro variable into a date.

Notice that, you don’t need to write the first argument of the INPUTN function between (double) quotes.

You can also convert a string directly into a macro date.

%let date_number = %sysfunc(inputn(31DEC2020, date9.));
%put &=date_number.;

Above, we assumed that the text string contained a date of the DATE9. format. However, this isn’t always the case. Below we show two examples of how to convert dates with other formats (such as mm-dd-yyyy and dd/mm/yyyy).

Convert a MM-DD-YYYY string into a SAS date

%let date_number = %sysfunc(inputn(12-31-2020, mmddyy10.));
%put &=date_number.;

Convert a DD/MM/YYYY string into a SAS date

%let date_number = %sysfunc(inputn(31/12/2020, ddmmyy10.));
%put &=date_number.;