SAS does not treat text strings like dates, even if they look like them. Thus, you cannot perform operations on dates stored as strings. As a result, you need to convert the character date to SAS Date to work with them.
How to Convert character date to SAS Date?
It is crucial to determine in what format the date is stored before converting it into a SAS date. Or, to put it more precisely, what informat does the INPUT function need to read the variable to convert it correctly?
Thus, the conversion is a two-step process:
- Identify the format in which the text date is stored.
- Convert the character variable to a valid SAS date using the INPUT function.
For more details on SAS date format and informats, see our post on SAS date formats.
data ex1;
dt="11/16/1989";
dt2=input(dt,MMDDYY10.);
run;Note that yymmdd6. is an informat in this statement
Print without format:
Print with format (YYMMDD10.):
It is important to create a new variable for conversion. By using the same variables as before, variables will remain characters.
Convert to Different Date Format
We originally stored the data in Month-Date-Year-11/16/1989 format, but as a character. Using the SAS date format, we can convert it to Date-Month-Year.
format dt1 ddmmyyD10.;
Convert Multiple Character Variables to Date
Consider the case where you must convert multiple character variables to SAS datevalue format. It is possible to convert them using SAS arrays.
data ex2;
input dt: $10. dt2: $10.;
cards;
14-04-29 2019-11-16
15-02-05 2022-12-01
16-11-22 2022-12-07
16-12-21 2022-12-08
17-10-19 2022-12-15
;
Here is the SAS Code to covert all character dates to SAS dates
data out;
set ex2;
array olddates $ dt1 dt2;
array newdates dt3 dt4;
do i = 1 to dim(olddates);
newdates(i) = input(strip(olddates(i)),yymmdd10.);
end;
drop i;
format dt3-dt4 yymmdd10.;
run;
Using the INPUT function in PROC SQL
You must first convert the text date into a numeric SAS date using the input function, and then you can attach a format to the result to show how you want this SAS date to be printed.
proc sql;
create table newdt as Select input(dt, mmddyy10.) as date format=weekdate.
from ex1;
Quit;How to Convert a String into a Datetime?
A text string can also be converted into a datetime, just as you can convert it into a date.
In SAS, a DateTime variable is the number of seconds between midnight on January 1, 1960, and a specific date, including the hour, minute, and second.
For example, a number such as 1925078399 represents December 31, 2020, at 23:59:59.
With the INPUT function, you can convert a string that looks like a datetime (like 31DEC2020 23:59:59) into a SAS datetime variable.
This function needs a text string and an informat as its two arguments. The informat tells SAS how to read the string of text.
INPUT(text_string, datetime_format);
data ex3;
length datetimechar $30;
datetimechar= "07SEP2022 23:59:59";
datetime_num = input(datetimechar, DATETIME.);
datetime_num_fmt=datetime_num;
output;
datetimechar = "09.15.2012 03:53:00 pm";
datetime_num = input(datetimechar, mdyampm25.);
datetime_num_fmt=datetime_num;
output;
format datetime_num_fmt datetime23.;
run;
How to convert character to date in SAS SQL?
Converting a character variable to a date variable in SAS SQL can be done using the INPUT function.
The INPUT function allows you to convert character data to numeric data by specifying the appropriate informat. For date conversions, you’ll need to use date informats like MMDDYY10., DDMMYY10., YYMMDD10., etc., depending on the format of your character date.
First, let’s create a dataset with a character date variable char_date in the format MM/DD/YYYY.
data my_table;
input char_date $10.;
datalines;
01/01/2020
02/15/2021
03/30/2019
;
run;
proc sql;
create table new_table as
select *,
input(char_date, MMDDYY10.) as num_date format=MMDDYY10.
from my_table;
quit;How to Handle Missing or Incorrect Dates?
Handling incorrect or missing dates is crucial to ensure that your SAS programs run without errors. You can use the ?? modifier with the INPUT function to prevent errors when converting character dates to numeric dates.
First, let’s create a dataset with a character date variable char_date that includes some incorrect and missing values. The format is supposed to be MM/DD/YYYY.
data my_table4;
input char_date $10.;
datalines;
01/01/2020
02/15/2021
03/30/2019
INVALID
.
;
run;
In this dataset, INVALID is an incorrect date, and . represents a missing date.
The ?? operator is used to suppress errors in the DATA step, but it’s not directly applicable in PROC SQL. However, you can use a DATA step to perform the conversion.
data my_table4_converted;
set my_table4;
num_date = input(char_date, ?? MMDDYY10.);
format num_date MMDDYY10.;
run;In this example, the DATA step is used to create a new dataset my_table4_converted where the char_date is converted to num_date using the ?? operator to suppress errors.
How to read raw data as SAS Date variables?
Raw data can be read into SAS date variables using the appropriate informat.
If you want to be able to display the names in a human-readable format, it is necessary to assign an appropriate format to the variable DATE.
data dates;
input date yymmdd6.;
format date yymmdd10.;
cards;
220901
220902
220903
;
run;
How to read multiple date values from raw data?
For data that isn’t standard (like commas, dollars, dates, etc.) or is longer than 8 bytes when using the List input technique, you would need to use either the INFORMAT statement or the colon modifier with the INPUT statement.
Method 1: Using the INFORMAT or ATTRIB Statement: Use the INFORMAT or ATTRIB Statement to assign a format to the input variables.
data ex4;
informat date1 ddmmyy10. date2 mmddyy10.;
input date1 date2;
format date1-date2 yymmdd10.;
datalines;
05/09/2022 12/30/2022
06/09/2015 12/31/2022
;
run;
Method 2: Use Colon (:) input modifier
data ex5;
input date1: ddmmyy10. date2: mmddyy10.;
format date1-date2 yymmdd10.;
datalines;
05/09/2022 12/30/2022
06/09/2015 12/31/2022
;
run;One format to read any dates in SAS.
With the ANYDTDTE format, it’s possible to input many messy date values easily.
data Dates;
input @1 date $21. @22 dateinfo $10. formatted_date anydtdte21.;
FORMAT formatted_date DATE9.;
datalines;
08SEP22 DATE 08SEP22
08SEP2022 14:30:08.5 DATETIME 08SEP2022 14:30:08.5
08092022 DDMMYY 08092022
20120908 JULIAN 20120908
08/09/22 DDMMYY 08/09/22
SEP2022 MONYY SEP2022
14:30 TIME 14:30
20220908 YYMMDD 20220908
22q4 YYQ 12q4
Sep 8, 2012 none Sep 1, 2012
September 8, 2012 none September 1, 2012
;
run;
As you can see, the ANYDTDTE informat reads 11 different strings but converts all of them to the SAS date value.
Summary
It is so important that we emphasize it again. It is not recommended to store date values as character variables. Dates stored as characters will lose their computational power since they are text values. Furthermore, you cannot sort them through time in a meaningful way. Dates will sort them alphabetically if you sort them, which will make no sense.
