How to convert characters that are not in a proper date format into SAS date?

[sylvia106]

Hi, 

I have character variables like these:
1/25/2017 4:49:01 PM

12/1/2017 12:07:00 PM

I want to convert the date part into SAS date. The problem is there are no "0" before months and days (for example they should be like "01/25/2017 4:49:01 PM"). Since MDY are not in certain digits, I can't use "SASDATE = MDY(SUBSTR......" to extract the date properly.

I know it sounds simple but I can't find a solution so far. Hope there's a way to solve this!

Thanks a lot!

[Shmuel]

Solution is given in next link:

https://communities.sas.com/t5/SAS-Programming/Datetime-format/m-p/375750 

[georgel]

Hi please try this :I just made some nesting for fiinding the "/" 

data tocorrect;
input date_to_amend $30. ;
datalines;
1/25/2017 4:49:01 PM
12/1/2017 12:07:00 PM

;
run;

data corrected ;
   set  tocorrect;
    format  sasdate DATE9.;
   format month 8.;
   format day 8.;
   format year 8.;
   month=substr(date_to_amend,1, find(date_to_amend,"/")-1);
   day=substr(substr(date_to_amend, find(date_to_amend,"/")+1,length(date_to_amend)),1, find(substr(date_to_amend, find(date_to_amend,"/")+1,length(date_to_amend)),"/")-1);
   year = substr(substr(date_to_amend,1, find(date_to_amend," ")-1),length(substr(date_to_amend,1, find(date_to_amend," ")-1))-3,4);
   sasdate=mdy(month,day,year);
run;

[sylvia106]

Thank you!! This one took me some time to understand but it works! I think this method is useful for dealing with messy data 🙂

[Kurt_Bremser · Accepted solution]

Try this:

sasdate = input(scan(chardate,1," "),mmddyy10.);
format sasdate yymmdd10.;

[sylvia106]

Thank you so much!! It works well with just some simple lines!

[singhsahab]

data have;
input date_to_amend $30. ;
datalines;
1/25/2017 4:49:01 PM
12/1/2017 12:07:00 PM
;
run;

data want;
set have;
New_Date=PUT(input(scan(date_to_amend,1,' '),MMDDYY10.),MMDDYY10.)||' '||strip(scan(date_to_amend,2,' ')||' '||scan(date_to_amend,3,' '));
run;

[novinosrin]

Hi @sylvia106  Sorry late to the party. Your pattern seems pretty straight forward and convenient to just read the datepart  with the appropriate informat. In your sample, the date part of the date-time character value appears to be in the form MMDDYY. 

What you could do is, just read upto 10 bytes of character by specifying the informat width as 10 i.e. mmddyy10. . Internally, SAS reads 10 bytes of char which in other words is a non-standard date. The pre-compiled INFORMAT will look up i.e. assign the appropriate integer date value. Then, you can of course assign a format of your choice. 

data tocorrect;
input date_to_amend $30. ;
datalines;
1/25/2017 4:49:01 PM
12/1/2017 12:07:00 PM
;
run;

data want;
 set tocorrect;
 sas_date=input(date_to_amend,mmddyy10.);
 format sas_date date9.;
run