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.
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);
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
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.
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".
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.
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
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.