DATA Step, Macro, Functions and more

How To - > Convert Variable from Character to Datetime.

Accepted Solution Solved
Reply
Super Contributor
Posts: 395
Accepted Solution

How To - > Convert Variable from Character to Datetime.

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;

 Mistake.png

Thank you.


Accepted Solutions
Solution
‎08-31-2017 02:37 PM
Trusted Advisor
Posts: 1,117

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

[ Edited ]

Editor's Note: Since the character date does not conform to a given SAS date informat, the code provided by @FreelanceReinhard 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);

 

 

 

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

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

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

Super Contributor
Posts: 395

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

[ Edited ]

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.

Super User
Super User
Posts: 7,942

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

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".

 

Super Contributor
Posts: 395

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

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. 

Super User
Posts: 19,770

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

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

 

Solution
‎08-31-2017 02:37 PM
Trusted Advisor
Posts: 1,117

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

[ Edited ]

Editor's Note: Since the character date does not conform to a given SAS date informat, the code provided by @FreelanceReinhard 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);

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 2937 views
  • 1 like
  • 4 in conversation