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
Yes, use the proper format, which in this case would be mmddyy10.
List of all date and time formats available in SAS:
@AnneLK wrote:
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
Are the dates of in the current problem supposed to be before 01JAN1960? Give an example of dates are you expecting.
How do the dates appear in RedCap? Can you show us a screen capture (use the "Insert Photos" icon to include your screen capture in your reply)
How do the dates appear in Excel? Can you show us a screen capture?
Here is shown 2 correct variables (diagnose_date debuskopi_date) that looks as in the RedCap database
followed by a wrong (ppiskopi_start) and
one right again (ppiskopi_date)
Hello, in your original post, you talked about numbers such as 52396 (that's the exact number you stated) but I don't see any such numbers above 50,000. Where does this 52396 come from?
The numbers now showing are (for example) 42760, and this produces reasonable dates after you add + '30dec1899'd as you showed. All you have to do after adding that value is assign format date9. to the variable PPISKOPI_START (or any other date format you would like).
data test5; set des2;
end_date_2= input(end_date,32.) + '30dec1899'd;
format end_date_2 date9.;
run;
For future questions, I strongly recommend using actual data (if possible), rather than made up data, which only confuses things. In the cases of dates, with no other context and no identifying information, there is no reason to provide us with a made up a number.
Yes, use the proper format, which in this case would be mmddyy10.
List of all date and time formats available in SAS:
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.