How to Read Datetime in Sas Sql
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 part. 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 appointment, we bear witness how to create a datetime variable from a string. We also demonstrate how to utilise the INPUT office in PROC SQL and how to convert a macro variable into a SAS appointment.
If you have a number that looks like a appointment (e.g., 01012020), then we recommend reading this commodity.
What is a Date in SAS?
A SAS engagement represents the number of days between January i, 1960, and a specific date. Hence, a engagement in SAS is a numeric variable, non a character string. Therefore, to carry out operations with dates, yous showtime need to brand certain that they are saved as a numeric variable.
Since SAS stores a date equally a number, it is difficult for humans to brand sense of it. For example, the number 22.280 represents December 31, 2020. Therefore, SAS uses formats to display these numbers every bit dates and make them understandable for the human center.
data work.ds; input date_number; date_format = date_number; format date_format date9.; datalines; -one 0 1 2 22280 ; run; 
Although you won't easily attain them, SAS dates take their limits. Y'all can only work with dates in SAS between January one, 1582, and Dec 31, 19900.
How to Convert a String into a Date?
Even though a text string might wait like a date, SAS doesn't care for them as such. Every bit a issue, you tin can't bear out operations with dates that are stored as strings. Hence, you first need to convert them into an bodily date before you tin can work with them.
You can convert a text cord into a SAS appointment with the INPUT function. The INPUT function reads a string that looks similar a appointment, and given its format (east.g., mm-dd-yyyy or ddmmmyyyy), returns a number. This number represents a date in the SAS linguistic communication.
INPUT(text_string, date_format);
The 2d argument of the INPUT function (i.eastward., date_format) is critical when yous catechumen text into a engagement. This argument specifies how SAS interprets the text cord of the commencement statement. For example, if your text string is 12/31/2020, and so you need the MMDDYY10. format as the second argument.
Discover that, if you lot employ an wrong format, the INPUT function returns a missing value, or worse, returns a numeric value that doesn't represent to the date in the text string. For example, SAS could translate 11/x/2020 as Oct 11, 2020, or November ten, 2020. So, exist careful with the format you use.
The table beneath shows how to convert text strings of different formats into a SAS date.
| Text String | Format | SAS Lawmaking to Convert Cord to Date |
|---|---|---|
| 31DEC2020 | DATE9. | INPUT('31DEC2020′, DATE9.) |
| 31/12/2020 | DDMMYY10. | INPUT('31/12/2020′, DDMMYY10.) |
| 31:12:2020 | DDMMYY10. | INPUT('31:12:2020′, DDMMYY10.) |
| 31-12-2020 | DDMMYY10. | INPUT('31-12-2020′, DDMMYY10.) |
| 31122020 | DDMMYY10. | INPUT('31122020', DDMMYY10.) |
| 31 12 2020 | DDMMYY10. | INPUT('31 12 2020′, DDMMYY10.) |
| 12/31/2020 | MMDDYY10. | INPUT('12/31/2020′, MMDDYY10.) |
| 12:31:2020 | MMDDYY10. | INPUT('12:31:2020′, MMDDYY10.) |
| 12-31-2020 | MMDDYY10. | INPUT('12-31-2020′, MMDDYY10.) |
| 12312020 | MMDDYY10. | INPUT('12312020', MMDDYY10.) |
| 12 31 2020 | MMDDYY10. | INPUT('12 31 2020′, MMDDYY10.) |
| 2020/12/31 | YYMMDD10. | INPUT('2020/12/31', YYMMDD10.) |
| 2020:12:31 | YYMMDD10. | INPUT('2020:12:31', YYMMDD10.) |
| 2020-12-31 | YYMMDD10. | INPUT('2020-12-31', YYMMDD10.) |
| 20201231 | YYMMDD10. | INPUT('20201231', YYMMDD10.) |
| 2020 12 31 | YYMMDD10. | INPUT('2020 12 31', YYMMDD10.) |
| 2020/31/12 | YYDDMM10. | INPUT('2020/31/12', YYDDMM10.) |
| 2020:31:12 | YYDDMM10. | INPUT('2020:31:12', YYDDMM10.) |
| 2020-31-12 | YYDDMM10. | INPUT('2020-31-12', YYDDMM10.) |
| 20203112 | YYDDMM10. | INPUT('20203112', YYDDMM10.) |
| 2020 31 12 | YYDDMM10. | INPUT('2020 31 12', YYDDMM10.) |
Adjacent, we provide some examples that apply the SAS code to a higher place.
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 ii dates. However, the numbers in the new column aren't like shooting fish in a barrel to interpret. Therefore, we volition utilise a format to make these values understandable for the man heart.
For example, in the code below nosotros 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; 
In that location be numerous formats for SAS date variables. The table below shows the nigh popular ones.
| Date Format | SAS Date |
|---|---|
| 31DEC2020 | DATE9. |
| 31/12/2020 | DDMMYYS10. |
| 31:12:2020 | DDMMYYC10. |
| 31-12-2020 | DDMMYYD10. |
| 31 12 2020 | DDMMYYB10. |
| 31122020 | DDMMYYN8. |
| 12/31/2020 | MMDDYYS10. |
| 12:31:2020 | MMDDYYC10. |
| 12-31-2020 | MMDDYYD10. |
| 12 31 2020 | MMDDYYB10. |
| 12312020 | MMDDYYN8. |
| 2020/12/31 | YYMMDDS10. |
| 2020:12:31 | YYMMDDC10. |
| 2020-12-31 | YYMMDDD10. |
| 2020 12 31 | YYMMDDB10. |
| 20201231 | YYMMDDN8. |
How to Convert a Cord into a Datetime?
Similar to converting a text cord into a date, you can as well convert a text string into a datetime.
A SAS datetime variable in the number of seconds between midnight Jan ane, 1960, and a specific date including hour, minute, and second. For example, the number 1925078399 represents December 31, 2020, at 23:59:59.
You catechumen a cord that looks similar a datetime (e.m., 31DEC2020 23:59:59) into a SAS datetime variable with the INPUT part. This role requires 2 arguments, namely the text cord 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. Normally, one uses the DATETIME. format to read in a datetime text string. In the example below, we show how to utilize 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 xi:59:59 AM"; datetime_number = input (datetime_string, DATETIME30.); output; datetime_string = "31DEC2020 xi: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 argument to brand 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 tin can employ the DATETIME function to easily create a datetime variable. The reverse is besides true, you can create a appointment variable from a datetime variable with the DATEPART office.
How to Convert a String into a Engagement in PROC SQL?
So far, we take demonstrated how to convert a string into a engagement(time) variable in a SAS Information Footstep. However, y'all tin likewise practise this with PROC SQL.
Converting a text string into a appointment(time) in PROC SQL is similar to the same operation in a SAS Information Step. You utilise the INPUT function followed past the cord you want to convert and the informat of the appointment(time) variable. Equally a effect, SAS returns the number that represents your date(fourth dimension) variable.
The SAS code below shows how to convert a cord 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.) every bit date_number_format format=ddmmyy10. from work.ds; quit; 
Instead of converting a text string into a SAS date, y'all can use PROC SQL also to catechumen a sting into a datetime variable. The instance beneath shows how nosotros use the INPUT function to read the text cord.
data piece of work.ds; datetime_string = "31DEC2020 23:59:59"; output; datetime_string = "03JUL2021 17:30:00"; output; datetime_string = "10OCT2021 04:15:thirty"; 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 at present, we have discussed how to catechumen a text string (either hardcoded text or a variable) into a date variable. Even so, 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:
- You have a macro variable that looks like a date (east.yard., 31DEC2020), and
- Yous want to create a new macro variable that contains the numeric representation of this appointment (e.1000., 22280)
In this example, yous convert a macro variable into a date with the SYSFUNC function and the INPUTN function. Start, the SYSFUNC function lets you utilise SAS functions exterior a SAS Data Step or PROC SQL. And so, with the INPUTN function you convert the date-looking macro variable into a real macro appointment.
The INPUTN function is similar to the INPUT office because they both convert text into numbers. However, you tin use the INPUTN function in a %LET statement, whereas you can't exercise this with the INPUT part.
In the example below, we first utilize the %LET statement to create a macro variable. And so, we use the SYSFUNC and INPUTN functions to catechumen this macro variable into a date.
%permit date_string = 31DEC2020; %let date_number = %sysfunc(inputn(&date_string., date9.)); %put &=date_string.; %put &=date_number.;
Notice that, you lot don't need to write the beginning argument of the INPUTN function between (double) quotes.
You tin can besides convert a string directly into a macro engagement.
%allow date_number = %sysfunc ( inputn (31DEC2020, date9.) ); %put &=date_number.; 
Above, nosotros assumed that the text string contained a date of the DATE9. format. Yet, this isn't always the instance. Below we show two examples of how to convert dates with other formats (such equally mm-dd-yyyy and dd/mm/yyyy).
Convert a MM-DD-YYYY cord into a SAS engagement
%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.; 
Source: https://sasexamplecode.com/how-to-easily-convert-a-string-into-a-date-in-sas/
0 Response to "How to Read Datetime in Sas Sql"
Postar um comentário