If you have a string column with multiple date formats in it, the following code can be used to convert all the strings into a proper date format and store in a separate date column. The code is written in SAS Studio in SAS Viya 4.0. Columns with multiple date format before running the code: Following code will read both the columns and apply the conditions to parse and convert date and create new date column with correct date format: data find ;
set j;
run;
data want;
set j;
PA_DOE_01=input(PA_DOE,??32.);
if not missing(PA_DOE_01) then PA_DOE_01=int(PA_DOE_01)+'30dec1899'd;
if missing(PA_DOE_01) then PA_DOE_01=input(prxchange('s/MONDAY|TUESDAY|SUNDAY//i',-1,PA_DOE),anydtdte40.);
if missing(PA_DOE_01) then PA_DOE_01=input(tranwrd(PA_DOE,'00:00 ',' '),anydtdte40.);
PA_DOI_01=input(PA_DOI,??32.);
if not missing(PA_DOI_01) then PA_DOI_01=int(PA_DOI_01)+'30dec1899'd;
if missing(PA_DOI_01) then PA_DOI_01=input(prxchange('s/MONDAY|TUESDAY|SUNDAY//i',-1,PA_DOI),anydtdte40.);
if missing(PA_DOI_01) then PA_DOI_01=input(tranwrd(PA_DOI,'00:00 ',' '),anydtdte40.);
format PA_DOE_01 PA_DOI_01 date9.;
run; After running the above code, the two output columns PA_DOI_01 and PA_DOE_01 are the date columns with correct date format.
... View more
