Convert char datetime to sas date format with EU format

[yelkenli]

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. 

[LeonidBatkhan · Accepted solution]

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.

[PGStats]

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

[yelkenli]

@PGStats 

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

[PaigeMiller]

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!

[yelkenli]

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. 

[Kurt_Bremser]

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.

[yelkenli]

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.  

[PGStats]

Look at the DATESTYLE system option. This could be why ANYDTDTM. works for some and not for others.

https://documentation.sas.com/?docsetId=nlsref&docsetTarget=n0cmg3tjkn6zmbn1od6pna244zm7.htm&docsetV... 

[DavePrinsloo]

I think your main issue has been answered, but have a look at the formats starting with E8601 (Eg. E8601DN10.)
Also look into formats NLDATE or YYMMDDP or YYMMDDN.
The datetime19. formats mentioned elsewhere have English month names in them, and after Brexit, only the Irish speak English in the EU 😞

[Kurt_Bremser]

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;