BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
turcay
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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);

 

 

 

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

turcay
Lapis Lazuli | Level 10

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

turcay
Lapis Lazuli | Level 10

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. 

Reeza
Super User

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

 

FreelanceReinh
Jade | Level 19

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);

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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