From RedCap via .xlsx to sas: Dates comes in wrong format of 5 digits in stead of mm/dd/yyyy

[AnneLK]

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

[PaigeMiller]

---

@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.

[AnneLK]

This is how they should be:
09/03/2018
28/04/2018
29/04/2018
29/04/2018
29/04/2018
29/04/2018
29/04/2018
None of the dates are before 1997

[PaigeMiller]

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?

[AnneLK]

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)

[PaigeMiller]

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).

[AnneLK]

You are right: Values are rarely above 42800 and never >50000 (example below)

Using this code, I end up with numbers 17000-20000:
data test5; set des2;
end_date_2= input(end_date,32.) + '30dec1899'd;
run;

I am not sure where the code "date9." should go?
Can you write an example?

One og the variables look like this:
39644


39660
41356
40045
40120
40365
40569
40623
40406
40626
40609
40651
40731
41961
41010
41324
40966
41031
41442

40928
41031
41928
42402
42884


41583

41064
41249

41528
41036
41765
41041

41079
41240
41372
41235

42135
41100

41098

41141

43048
41571
41443
41331

42326

41297
41708
42165
41401
41418
41348
42018
41703
41541
41345
41443
41394
41449



43224







42124





42396
42641
43214
41469
41674

41598
41510
41870
41542

42461
42451
41541


41978

41716
41814
41590
42639
43195

41957
41628

41772
41647
41710
43234
41687


41915
41683

42143


42144

41936

41879
42094

41934


42373
41868
42464
42458
43410



41891
42131
41984


41991
42214

42031
42458



42072

42473

42151

43228
42227
42110

42093
42080

42032
42355
42383
42085
42593
42452



44767
42824
43035






42134

42794
42502

42317
42811

42594

42285

42417
42321
42276




42859
42692

42431


42395


43325

42509
42507

42503


42469
43172
42684
43374
42767
42573
42850

43424

43374
42703
42999









43375

42628
42773


42704
43088



42796
43201

42809
42723



43020
42822

42884

42825





42849



43130

43059


43328

42971
42950


42915
43228
42996

43004
43328
42863

43328
43213




43084
42775
43327

43782


43091




43553
43081


43326

43102
43148
43333


43594


43803











































43612
43430


43208
43301


43321
43326
43286
43398


43335
44097


43376

43419
43360





44627



43356


43482

43446
43788
43496
43818




43503
43474

43556
43591
43494

44637

43509
43630

44302

43804


43272
44001
43556
44631
43623

43343

43426
43815


44636

44637
43543


44274
43650
43796

44670

44637
43488


43508
43993







44636
43614

43689

44014
43655
43619

44785

44067
43710
44644

43675
44161





43685
43815








44006

43816
43783

44645
43763

44648

44077

44648
43896
43802
43732

44180
43748
43817
44130

43881

44039

43766


44144
43894



43895

43922

43892
44693
44008
44056

44651
44179
43844
44651





44151


44915


44797
43903







43986


44642


44060



44420

44200



44266
44278

44228
44365
44280


44334
44545

44489
44448
44484
44361






44866
44467



44866

44571

44594
44684

44552

44733





43978
44008
44273



43994
43962

44138

44541




44119
44154
44382




44256
44474
44130


44537

44159


44351
44299







44382

44526
43926
44426




44644
44691






44523
44812


42041
41663
41859


43010


42741

41474
41887
42388

41607

42009
41957
42013
42380

41687
41764

41754
42044



42695


43049

41792
42415

42384

42783
41992
41918





42464

42153







42391



44585
42667



43059




42755

43136



42994


43794

43024
43024


43773
43045
43748


43483
42499
42772
42741

43595





43413

42888






42905

43504

42888
44302

43948




43024

44673



43560


43770

43850

43598
43364




43469


43542

43612

44687

43584



43836





43770



43609





43406






43938

43777

43836

44172

44540

44470
43762




44596

44204







43966



44284






44501












1120-07-02
44215




44075






44606

[PaigeMiller]

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.

[AnneLK]

Thank you.

The format returned is seen below
Can it be changed to mm/dd/yyyy?

15JUL2008
.
.
31JUL2008
23MAR2013
20AUG2009
03NOV2009
06JUL2010
26JAN2011
21MAR2011
16AUG2010
24MAR2011
07MAR2011
18APR2011
07JUL2011

[PaigeMiller · Accepted solution]

Yes, use the proper format, which in this case would be mmddyy10.

List of all date and time formats available in SAS: 

https://documentation.sas.com/doc/en/pgmmvacdc/9.4/allprodslang/syntaxByCategory-format.htm#p0aa41u6...

[Tom]

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