Excel is the problem here. Is there an option in REDCAP to export as CSV instead? Or does it have a direct to SAS option?
What you are seeing is what SAS does when a column in the spreadsheet has mixed dates (numbers) and character strings. So the 5 digit strings you are seeing is the number that Excel uses to store dates converted to a text string. So just convert them back into a number and adjust for the difference in the base dates used.
Example:
data have;
input datestring $20. ;
cards;
1120-07-02
12-30-2022
30-12-2022
44215
;
data want;
set have;
datenum = input(datestring,?32.) + '30DEC1899'd ;
if missing(datenum) then datenum=input(datestring,anydtdte20.);
format datenum yymmdd10.;
run;
results
Obs datestring datenum
1 1120-07-02 .
2 12-30-2022 2022-12-30
3 30-12-2022 2022-12-30
4 44215 2021-01-19
... View more