topic Convert varchar variable to datetime format in New SAS User

Convert varchar variable to datetime format

jayvisw11 — Mon, 25 Oct 2021 15:47:52 GMT

Hi,

I have a date variable (eventtime) in my table in varchar format Eg:2021-10-18T16:33:30.388148Z. how can i convert the data type from varchar to utc date? Thank you!

Re: Convert varchar variable to datetime format

ballardw — Mon, 25 Oct 2021 17:07:33 GMT

VARCHAR where? Basic SAS data sets only have Character and Numeric? Is this in an external database? FedSQL?

Create a new variable using input with the correct informat.

There are Informats E8601DZw.d and E8601LZw.d, depending on whether you want a standard timezone or local for the Z,

I suspect you might want:

newvar = input (yourunamedvariable,E8601dz27.6);

Assign a desired format, possibly one of the E8601 flavors, for people to understand.

Is the time actually accurate to a millionth of a second???

Re: Convert varchar variable to datetime format

Tom — Mon, 25 Oct 2021 17:28:00 GMT

@ballardw wrote:

...

Is the time actually accurate to a millionth of a second???

Not if the dates are after the middle of 1988.

40    data test;
41      dt=constant('exactint')/10E6 ;
42      put dt=comma32.6 /
43          dt=datetime27.6
44      ;
45    run;

dt=900,719,925.474099
dt=16JUL1988:23:58:45.474099

Re: Convert varchar variable to datetime format

jayvisw11 — Mon, 25 Oct 2021 17:32:27 GMT

Hi,

Thank you for your reply. It is table created using FedSQL from an external database. E8601dz27.6 is the format of the value.

data casuser.reporting_table_1;
set casuser.reporting_table_1;
eventtime_1 = input(eventtime,E8601dz27.6);
format eventtime_1 E8601DT.;
drop eventtime;
rename eventtime_1 = eventtime;
run;

When I ran this code, I still see that the eventtime variable data type is double instead of a datatime format.

Re: Convert varchar variable to datetime format

Kurt_Bremser — Mon, 25 Oct 2021 17:33:12 GMT

On z/OS, it works because of the extra 4 bits of the mantissa.

Re: Convert varchar variable to datetime format

Tom — Mon, 25 Oct 2021 17:40:00 GMT

Does CAS support data types that SAS does not support? In regular SAS datasets there are only two data types. Fixed length character strings and floating point numbers. The format attached to a variable just determines how the values are displayed as text, not the type of variable the values are stored in.

DATETIME values are just floating point numbers which are interpreted as the number of seconds since 1960.

Re: Convert varchar variable to datetime format

ballardw — Mon, 25 Oct 2021 18:25:54 GMT

@jayvisw11 wrote:
Hi,

Thank you for your reply. It is table created using FedSQL from an external database. E8601dz27.6 is the format of the value.

data casuser.reporting_table_1;
set casuser.reporting_table_1;
eventtime_1 = input(eventtime,E8601dz27.6);
format eventtime_1 E8601DT.;
drop eventtime;
rename eventtime_1 = eventtime;
run;

When I ran this code, I still see that the eventtime variable data type is double instead of a datatime format.

The question would be does the formatted value look correct? The default length for the format you chose is 19 so the fractional seconds are not displayed. If the value is acceptable then your golden. Just remember your values will contain fractional seconds if you use them in comparisons and may yield the occasional unexpected result depending on what you compare them to.