charater date to sas date formats

[anirudhs]

 hi all i am looking to convert the non homogeneous character date to date format. following is the data examples of the dates in character form. 14/2/2019 6:37 PM 5/2/2019 14:05 2/2/2019 12:20 1/2/2019 15:00 2/2/2019 12:47 11/2/2019 12:16 19/2/2019 3:41 PM 20/2/2019 10:59 AM 13/2/2019 2:03 PM so if i use the following formula then the dates with AM/PM is been converted input(Opened_date,anydtdte10.) format new_date date10. ;  

[RW9 · Accepted solution]

Simplest method I can think of is to standardise your data first off.  So - and you have not provided any test data in the form of a datastep, so nothing to work with - if you have a text string:

14/2/2019 6:37 PM

First, separate out the three parts, so you have a date, a time, and possibly an indicator.  Then if indicator is PM, add 12h to time.  Then you can combine date and time to get date/time.

data want;
  instr="14/2/2019 6:37 PM";
  dte=input(scan(instr,1," "),ddmmyy10.);
  time=input(scan(instr,2," "),time5.);
  indic=scan(instr,3," ");
  if strip(upcase(indic))="PM" then time=time+"12:00"t;
  wantdt=dhms(dte,hour(time),minute(time),0);
  format dte date9. time time5. wantdt datetime.;
run;
  

[anirudhs]

now any other solution where this can be done in one formula

[RW9]

I only split it up to clearly show the process, just combine the statements:

data want;
  instr="14/2/2019 6:37 PM";
  wantdt=dhms(input(scan(instr,1," "),ddmmyy10.),
              hour(input(scan(instr,2," "),time5.)+ifn(scan(instr,3," ")="PM","12:00"t,0)),
              minute(input(scan(instr,2," "),time5.)+ifn(scan(instr,3," ")="PM","12:00"t,0)),
              0);
  format wantdt datetime.;
run;

Or just run an if statement on the data and add in the missing AM/PM part.  The problem is you have data which does not conform to a structure, therefore you need to apply the rest of the structure.  If it was me, I would look at my import agreement, see the data did not match and then reject the data for being non-compliant.

[Jagadishkatam]

data have;
input date$20.;
newdate=input(prxchange('s/(\d+\/)(\d+\/)(\d+)/$2$1$3/',-1,strip(date)),MDYAMPM20.);
datepart=datepart(newdate);
format newdate datetime20. datepart date9.;
cards;
14/2/2019 6:37 PM
;

[BrunoMueller]

Using the ANYDTDTE or ANYDTDTM should just work as expected, see example below.

data want;
  infile cards;
  input someDate_c $32.;
  someDate = input(someDate_c, anydtdte32.);
  someDateTime = input(someDate_c, anydtdtm32.);
  format
    someDate date9.
    someDateTime datetime19.
  ;
cards4;
14/2/2019 6:37 PM
5/2/2019 14:05
2/2/2019 12:20
1/2/2019 15:00
2/2/2019 12:47
11/2/2019 12:16
19/2/2019 3:41 PM
20/2/2019 10:59 AM
13/2/2019 2:03 PM
;;;;