Character Date Conversion

[runi_r]

Hello,

I am trying to convert a character date time stamp with time zone information into a date time variable.

My date variable is stored in this format, "YYYY-MM-DD"T"HH24:MI:SS.ff3"Z.""  Here is an example value from my data set: 2022-03-09T14:00:00Z.

Ideally, I would like to change values to mmddyy10 or any date format.

Here is one proc sql method I tried: 

   proc sql;
      select to_char(to_timestamp_tz(2022-03-09T14:00:00Z, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'DD-MON- YY') FMT from rpt
   quit;

Here is my error: 

29 select to_char(to_timestamp_tz(2022-03-09T14:00:00Z, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'DD-MON-YY') FMT
___
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN,
OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

Any help is greatly appreciated!

[PaigeMiller]

Please, whenever you get an error in the LOG, show us the ENTIRE log for this PROC or DATA step. Do not pick and choose parts to show us and leave other parts of the log out. Please copy the log as test and paste it into the window that appears when you click on the </> icon.

Also, please tell us what PROC CONTENTS says about this variable in your data set. Does PROC CONTENTS say it is numeric or text?

Lastly, to_timestamp_tz is not a SAS function.

[runi_r]

1                                                    The SAS System

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='report2.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='.egp';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='.egp';
8          %LET _SASPROGRAMFILE='report2.sas';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP(9, 4, 4) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
21             ENCODING='utf-8'
22             STYLE=HtmlBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         


28            proc sql;
29               select to_char(to_timestamp_tz(2022-03-09T14:00:00Z, 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), 'DD-MON- YY')
                                                          ___
                                                          22
                                                          76
29       ! FMT from rpt;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, 
              AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, 
              OR, ^, ^=, |, ||, ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
30            quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
31         
32         %LET _CLIENTTASKLABEL=;
33         %LET _CLIENTPROCESSFLOWNAME=;
34         %LET _CLIENTPROJECTPATH=;
35         %LET _CLIENTPROJECTPATHHOST=;
36         %LET _CLIENTPROJECTNAME=;
37         %LET _SASPROGRAMFILE=;
38         %LET _SASPROGRAMFILEHOST=;
39         
2                                                    The SAS System

40         ;*';*";*/;quit;run;
41         ODS _ALL_ CLOSE;
42         
43         
44         QUIT; RUN;
45         

This is stored as a character variable in existing dataset.  I also stated this in title of my question.  More specifically format is $Char24.

Thanks!

[Santt0sh · Accepted solution]

Hi,
Please try this.

data rpt;
input timestp $20.;
datalines;
2022-03-09T14:00:00
run;

proc sql;
select input(timestp,anydtdtm21.) as FMT format = datetime18. from rpt;
quit;

[runi_r]

Hi, This worked for me.  Thanks so much!