Converting a string column with multiple date formats to a date column with a common date format

[sachinrathod14]

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.

 

[Tom]

You need to explain WHY that code works.

[ballardw]

You might also explain where the data set J actually comes from. 

There are hints that this involves Excel as a possible data source. Which may mean that manipulation of the data set in Excel prior to reading into a SAS data set might be a better solution such as setting a single date format in the Excel column. Or save to a CSV text file and use a data step to read the data.