topic Re: How to convert characters that are not in a proper date format into SAS date? in SAS Programming

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

sylvia106 — Sun, 03 Jan 2021 08:13:21 GMT

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!

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

Shmuel — Sun, 03 Jan 2021 08:45:15 GMT

Solution is given in next link:

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

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

georgel — Sun, 03 Jan 2021 09:36:54 GMT

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;

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

Kurt_Bremser — Sun, 03 Jan 2021 09:50:01 GMT

Try this:

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

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

singhsahab — Sun, 03 Jan 2021 10:25:45 GMT

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;

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

sylvia106 — Sun, 03 Jan 2021 10:40:03 GMT

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

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

sylvia106 — Sun, 03 Jan 2021 10:45:36 GMT

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

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

novinosrin — Sun, 03 Jan 2021 15:15:34 GMT

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