topic Re: How To - > Convert Variable from Character to Datetime. in SAS Programming

How To - > Convert Variable from Character to Datetime.

turcay — Fri, 18 Dec 2015 11:40:23 GMT

Hello everyone,

I'm trying to convert variable from character to DATETIME. format. When I use the following code the latest six digits were 0 while they should be "860184" . What should I do to see right result. Also I would like to ask you when I try to overwrite "Date" variable I got the value as "0" or missing so I assigned the value in new variable then I dropped the variable and changed the new ariable name as old variable name. Is it possible to overwrite the variable without assign the variables value to new variable.

DATA HAVE(Drop=Date Rename=(Date2=Date));
Date="1960-01-01-00.00.00.860184";
/*Date=input(Date,anydtdtm.);*/
Date2=input(Date,anydtdtm.);
FORMAT Date2 datetime27.6;
RUN;

Thank you.

Re: How To - > Convert Variable from Character to Datetime.

RW9 — Fri, 18 Dec 2015 12:08:45 GMT

Well, I don't recognise that date time format. You get a 0 because the date and time given is the start point from where SAS calculates date values. Remember under the hood, SAS stores dates as number of days since 01JAN1960, and datetimes and number of seconds since 01JAN1960 00:00, so unformated that date is 0, 01jan would be 1 ...

As for the other part, I would recommen changing whatever that format of date time is over to ISO standard datetime:

yyyy-mm-ddThh:mm:ss.ss

Re: How To - > Convert Variable from Character to Datetime.

turcay — Fri, 18 Dec 2015 12:29:04 GMT

Hello @RW9,

Well, when I try to convert to numeric variable it brings 0 I think it is related the date(1960) which I wrote in "Date" variable. But when I overwrite the variable as Datetime. format it brings missing value. What is the reason for that ??? Also, I couldn't understand why last 6 digits were zero even I've already put the value "860184" in the "Date" variable when I assign the value in new variable as Datetime.

Thank you.

Re: How To - > Convert Variable from Character to Datetime.

RW9 — Fri, 18 Dec 2015 13:37:53 GMT

Well, the date conversion is pretty straightforward. You assign date to be a character:

Date="1960-01-01T00:00:00.860184";

Then you try to put a number - that date is 0 numerically - into the character variable (i.e. implicit conversion).
Date=input(Date,anydtdtm.);

So date becomes the numeric value of the date you gave, which is "0". 02 Jan would be "1".

Re: How To - > Convert Variable from Character to Datetime.

turcay — Fri, 18 Dec 2015 13:56:58 GMT

Okay, lets think about value of "Date" is ->

Date="2000-12-12-00.00.00.860184";

and I take many datas from Excel or another environment as Datetime27.6 format. I mean the data which is located at Excel equal to Datetime27.6 format in SAS. When I Import these datas they come as Character type and I try to change the datas from Character to Datetime27.6 format but when I try it, it becomes as

Date="2000-12-12-00.00.00.000000";

but actual datas' last 6 digit include

.860184

so why I can't see the actual values and how can I fix it, how I can see whole of the actual value ???

Thank you.

Re: How To - > Convert Variable from Character to Datetime.

Reeza — Fri, 18 Dec 2015 15:50:16 GMT

You aren't importing the data correctly. I'm not sure what format you'll need to get your data in, a variant on E8601DT I imagine, or may need to format your date to match a present format....or create your own informat.

data example;
length date_char $30.;
date_char="2000-12-12T00:00:00.860184";
date_num_raw=input(date_char, e8601dt26.6);
date_num_fmt=date_num_raw;

format date_num_fmt datetime27.6 date_num_raw 26.6;
run;

proc print;run;

 Obs            date_char             date_num_raw                                 date_num_fmt

  1     2000-12-12T00:00:00.860184    2000-12-12T00:00:00.860184      12DEC2000:00:00:00.860184

Re: How To - > Convert Variable from Character to Datetime.

FreelanceReinh — Thu, 31 Aug 2017 18:37:08 GMT

Editor's Note: Since the character date does not conform to a given SAS date informat, the code provided by @FreelanceReinh is the best solution.

You can use the B8601DJ20.6 informat after removing the punctuation marks:

data have(drop=date rename=(date2=date));
date="2000-12-12-00.00.00.860184";
date2=input(compress(date, '-.'), b8601dj20.6);
format date2 datetime27.6;
run;

Instead of listing the punctuation marks explicitly in the second argument of the COMPRESS function, you can use the 'p' modifier to remove any punctuation marks (as well as blanks):

date2=input(compress(date,,'p'), b8601dj20.6);