How to change string format to date format in particular columns in Sas

I have a dataset which has 100 columns. A simpler & smaller version is as follows:

Account	Anticipated_Close_Date	Need_Date	Amount	Date_Accepted	Confirmed_Date	Comments
John	1/15/2024	1/15/2024	1000			Awaiting Acceptance
John	1/23/2024	1/28/2024	20000	1/20/2024	1/22/2024	Closed
Sam	12/25/2023
Peter	4/5/2024	4/12/2024	350	3/27/2024		Confirmation needed

The dataset has around 30 columns with various dates all in character format. I need to convert all of them into date format. Also, the number of columns with date information can change i.e there can be new addition or deletion.

I know:

data want;
set database;
format temp mmddyy10.;
temp = input(Anticipated_Close_Date, mmddyy10.);
drop Anticipated_Close_Date;
rename temp = Anticipated_Close_Date;

will give me the desired format. I have also identified all columns having 'Date' in them:

proc contents data = database out= contents noprint; run;

proc sql;
select name into :varlist seperated by ' '
from contents
where name like '%Date%';
quit;

I do not how to combine them both and run it over the entire dataset. My guess is we need to iterate over each column and check if the column is a part of 'varlist' and if true, use the input function.

Solution

You can use the :varlist to specify the elements of a DATA Step array and loop over the array. You will need to have some more SQL :into to create other code snippets needed to convert and rename.

Example:

Variables named *Date* are character strings and the same name should be used after converting the string to a SAS date value.

proc contents data = database out= contents noprint; run;

proc sql;
select 
  cats(quote(trim(name),"'"),'N=_',varnum)
, cats('_',varnum)
, cats(quote(trim(name),"'"),'N')
into
  :rename_in separated by ' '
  :temp_date_vars separated by ' '
  :date_vars separated by ' '
from contents
where name like '%Date%';
quit;

data want ;
  set database (rename=(&rename_in)) ;
  array tempdates &temp_date_vars ;
  array dates &date_vars ;
  do over dates ;
    dates = input(tempdates,mmddyy10.) ;
  end ;
  format &date_vars mmddyy10. ;
run;