I see many threads and documentation on this subject, but cannot find the correct input format for my problem.
My date time format in $CHAR23. is '28/02/2020 15:48:20,000'
the ',000' portion is not needed, so I can strip that out --> '28/02/2020 15:48:20'
Can someone point me to the correct method to translate this to a sas date time? the any-datetime method does not work as it gives Jan 3 for 01/03/2020 (should be March 1) and February 28 for 28/02/2020 in the same dataset. otherwise it returns without error.
data want (keep=mydatetime datetime tadt);
set have;
datetime=substr(mydatetime,1,19);
tadt=input(datetime, anydtdtm19.);
format tadt datetime19.;
run;
i have tried also with MMDDYY19. after stripping out the backslashes.
Hi yelkenli
You can use the following approach:
data have;
mydatetime = '28/02/2020 15:48:20,000'; output;
mydatetime = '01/03/2020 15:48:20,000'; output;
run;
data want (keep=mydatetime tadt);
set have;
datec = substr(mydatetime,1,10);
timec = substr(mydatetime, 12,8);
date = input(datec,ddmmyy10.);
time = input(timec,time8.);
tadt = dhms(date,hour(time),minute(time),second(time));
format tadt datetime19.;
run;
Hope this helps.
Hi yelkenli
You can use the following approach:
data have;
mydatetime = '28/02/2020 15:48:20,000'; output;
mydatetime = '01/03/2020 15:48:20,000'; output;
run;
data want (keep=mydatetime tadt);
set have;
datec = substr(mydatetime,1,10);
timec = substr(mydatetime, 12,8);
date = input(datec,ddmmyy10.);
time = input(timec,time8.);
tadt = dhms(date,hour(time),minute(time),second(time));
format tadt datetime19.;
run;
Hope this helps.
Try this:
data have;
mydatetimeStr = '28/02/2020 15:48:20,000'; output;
mydatetimeStr = '01/03/2020 15:48:20,000'; output;
run;
data want;
set have;
myDate = datepart(input(myDateTimeStr, anydtdtm.));
format myDate yymmdd10.;
run;
proc print data=want noobs; run;
mydatetimeStr myDate 28/02/2020 15:48:20,000 2020-02-28 01/03/2020 15:48:20,000 2020-03-01
Can you swap your two outputs and see if you get these results? I think SAS is reading the first line and determining where the month and day are, and applying that format to all rows. This is similar to the risk you get when importing data without controlling the format, SAS will use the first n observations to assign a format
data have;
mydatetimeStr = '01/03/2020 15:48:20,000'; output;
mydatetimeStr = '28/02/2020 15:48:20,000'; output;
run;
with your code I get:
mydatetimeStr myDate
01/03/2020 15:48:20,000 2020-01-03 28/02/2020 15:48:20,000 2020-02-28
When I ran it with my table, I got the year 1960.
1 01/03/2020 00:00:00,000 01JAN60:06
2 01/03/2020 00:00:01,000 01JAN60:06
3 01/03/2020 00:00:02,000 01JAN60:06
maybe there is some hidden special character in my data?
I get the same result if I strip out the ,000
You really need to share a portion of your raw data, and the EXACT code you are running.
You get 01JAN1960 because you don't have datetime values, you actually have date values, and the whole thread doesn't apply if you actually have date values. But maybe its your code that is incorrect, you do have datetime values but the wrong code. So ... share!
Thanks @PaigeMiller. Code is as above in my first post, which did not work, and then I tried the code from from @PGStats sans the first datastep, and changing only the table and variable name in the second to point to my table.
here are some of the observations in that variable (March 1rst):
01/03/2020 00:00:00,000
01/03/2020 00:00:01,000
01/03/2020 00:00:02,000
01/03/2020 00:00:03,000
01/03/2020 00:00:04,000
01/03/2020 00:00:05,000
01/03/2020 00:00:06,000
01/03/2020 00:00:07,000
01/03/2020 00:00:08,000
01/03/2020 00:00:09,000
01/03/2020 00:00:10,000
Thanks @DavePrinsloo
I will look further at the 8601 formats. my table is from a colleague in the EU.
With the data you gave us, the codes posted WORK. So, very obviously, your data seems to be different from what you posted.
To get code that works with your actual data, you need to give us the dataset as-is. The PROPER way to do this is, as always, a data step with datalines. Only then will we know exactly the attributes and real contents of your dataset.
thanks @Kurt_Bremser
I have ~5 million observations from a dataset that my validation team gives me. I am not an expert at SAS, so I simply copied and pasted a sample.
but from a more general perspective, I did not see how SAS chose US or EU date sequence (mdy or dmy) from the character variable using the any-date-time function. It looks like @PGStats answered that.
Look at the DATESTYLE system option. This could be why ANYDTDTM. works for some and not for others.
I don't like the "any" informats, as they will give unpredictable results depending on locale settings. Also see Maxim 31.
I am rather strict:
data have;
mydatetime = '28/02/2020 15:48:20,000'; output;
mydatetime = '01/03/2020 15:48:20,123'; output;
run;
data want;
set have;
mydate = input(mydatetime,ddmmyy10.);
mytime = input(scan(mydatetime,2," "),time8.);
mydt = dhms(mydate,0,0,mytime);
format
mydate yymmddd10.
mytime time8.
mydt e8601dt24.3
;
run;
The longer datetime format is used only to show that the fractional part of the time has been successfully stripped.
If the original data had the date in YMD order, it could be read directly with the e8601dt informat:
data have;
mydatetime = '2020-02-28 15:48:20,000'; output;
mydatetime = '2020-03-01 15:48:20,123'; output;
run;
data want;
set have;
mydt = input(mydatetime,e8601dt19.);
format
mydt e8601dt24.3
;
run;
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.