BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yelkenli
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
LeonidBatkhan
Lapis Lazuli | Level 10

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.

View solution in original post

10 REPLIES 10
LeonidBatkhan
Lapis Lazuli | Level 10

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
Opal | Level 21

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
PG
yelkenli
Calcite | Level 5

@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,0002020-01-03
28/02/2020 15:48:20,0002020-02-28

When I ran it with my table, I got the year 1960.  

101/03/2020 00:00:00,00001JAN60:06 
201/03/2020 00:00:01,00001JAN60:06 
301/03/2020 00:00:02,00001JAN60:06

maybe there is some hidden special character in my data?  

I get the same result if I strip out the ,000

PaigeMiller
Diamond | Level 26

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!

 

 

--
Paige Miller
yelkenli
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Opal | Level 21

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

PG
DavePrinsloo
Pyrite | Level 9
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
Super User

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 10 replies
  • 1708 views
  • 3 likes
  • 6 in conversation