SAS informat to convert "worddate along with time" into date variable

[Hari2]

Hi All,

Greetings. 

We have a scenario like, there is an external file with value as below. 

"January 27, 2020 10:55:00 AM"

"January 27, 2019 12:23:00 PM"

....

...

etc..

we need to validate this each time when we receive, whether this column has all values with same format (as mentioned above) or not. if not we need to flag them as error. this is the requirement along with many other with other variables. 

Question : So here, to convert above date values into sas date type, which date informat we can use . Please suggest.

(I know WORDDATE20. will read when we have only date in words. But we need SAS informat, which converts into sas date format, when both date and time is present as mentioned above).    

Thanks in advance. 

Regards,

Hari.

[Sajid01]

Please have a look at this forum post

https://communities.sas.com/t5/SAS-Programming/SAS-DATETIME-in-AM-PM-Conversion/td-p/337859 

[ballardw]

How critical is the time component of that value?

I ask because if who ever created that file used a 24 hour time instead of AM / PM then the ANYDTDTM informat would read that.

If the time component isn't critical and you only want a date:

data example;
   x="January 27, 2020 10:55:00 AM" ;
   /* remove AM or PM when present*/
   x = tranwrd(x,' AM','');
   x = tranwrd(x,' PM','');
   y= datepart(input(x, anydtdtm40.));
   format y date9.;
run;

Which would mean reading the initial value as character and then running the transformation and creation of date variable as above.

Tranwrd, if you have not used this function translates a "word", actually a sequence of characters, into another word. In this case removing AM or PM.

If you need the time set a flag to adjust the time after read.

data example;
   x="January 27, 2020 10:55:00 PM" ;
   /* Set a flag if PM*/
   PMFlag = ( index(x,' PM')>0);
   x = tranwrd(x,' AM','');
   x = tranwrd(x,' PM','');
   /* create datetime value */
   y= (input(x, anydtdtm40.));
   if pmflag then y= intnx('hour',y,12,'s');
   format y datetime22.;
run;

[average_joe]

I'm not sure why you say ANYDTDTM informat does not handle AM/PM times correctly. This snippet correctly reads 2:23 PM as 14:23.

28         data _null_;
29         dt_txt = 'January 27, 2019 02:23:00 PM';
30         dt = input(dt_txt, anydtdtm40.);
31         put dt= datetime.;
32         run;
dt=27JAN19:14:23:00

[ballardw]

Because in my current install of SAS 9.4m4 it doesn't.

531   data _null_;
532   dt_txt = 'January 27, 2019 02:23:00 PM';
533   dt = input(dt_txt, anydtdtm40.);
534   put dt= datetime.;
535   run;

dt=.

[average_joe]

Interesting. I found the problem note related to the issue you have. Says it was fixed in M6. It also says that if you drop the "M" in "AM/PM" it will work correctly. You should test that out.

[Tom]

What INFORMAT are you saying can read strings without the time?  WORDATE is a FORMAT, not an INFORMAT.

So you don't care about the time of day component in the strings?

Do you want to create DATE (number of days) or DATETIME (number of seconds) values?

[Kurt_Bremser]

I created an informat for the month names first, then use it in a data step. If any of the parts fails to convert, the macro variable error is set to 1 (true).

proc format;
invalue mymth
  "January" = 1
  "February" = 2
  "March" = 3
  "April" = 4
  "May" = 5
  "June" = 6
  "July" = 7
  "August" = 8
  "September" = 9
  "October" = 10
  "November" = 11
  "December" = 12
  other = 99
;
run;
  
%let error=0;

data want;
input datestring $35.;
date = mdy(input(scan(datestring,1),mymth.),input(scan(datestring,2),2.),input(scan(datestring,3),4.));
time = mod(input(scan(datestring,-2),time8.),43200);
if scan(datestring,-1) = "PM" then time = time + 43200;
x = _error_;
if _error_ then call symputx('error',1);
format
  date yymmdd10.
  time time8.
;
datalines;
January 27, 2020 10:55:00 AM
January 27, 2020 12:01:01 AM
January 27, 2019 12:23:00 PM
September 25, 2021 10:09:32 AM
xx 25,2021 09:23:00 AM
;