I have exported data from a RedCap database to SAS via an xlsx sheet .
Some dates are shown as characters in SAS in the format of 5 digits e.g. 52396, in the databse the format was mm/dd/yyyy
I would ask you to help me find a code, that converts the wrong date format (5 digits) to a date format of mm/dd/yyyy
Some years ago I had the same problem.
In SAS community I found out that there was a wrong offset in the excell conversion and could use this code to fix it:
data want; set have;
ppiskopi_date_2= input(ppiskopi_date,32.) + '30dec1899'd;
run;
Now, however this code results oin large negative numbers of 3-5 digits
Problem comes from:
I have exported data from RedCap as .xlsx
In the xlsx sheet the dato format is also wrong = 5 digits, although it was in the format mm/dd/yyyy in RedCap before I exported it
The reason for the xlsx error is that some entries in the database have been done wrong:
e.g. text in stead of a date or
02/03/2320 in stead of 02/03/2020
The result is that the date format in the excell sheet has been changed to 5 digits in stead of mm/dd/yyyy
