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

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

AnneLK — Fri, 20 Jan 2023 11:22:37 GMT

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

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

PaigeMiller — Fri, 20 Jan 2023 11:27:31 GMT

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

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

AnneLK — Fri, 20 Jan 2023 11:51:15 GMT

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

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

PaigeMiller — Fri, 20 Jan 2023 12:04:20 GMT

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?

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

AnneLK — Fri, 20 Jan 2023 12:15:16 GMT

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)

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

PaigeMiller — Fri, 20 Jan 2023 12:21:49 GMT

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

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

AnneLK — Fri, 20 Jan 2023 12:51:55 GMT

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

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

PaigeMiller — Fri, 20 Jan 2023 12:54:38 GMT

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.

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

AnneLK — Fri, 20 Jan 2023 13:01:45 GMT

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

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

PaigeMiller — Fri, 20 Jan 2023 13:04:47 GMT

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#p0aa41u6lidfz1n1n977se5xgl2s

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

Tom — Fri, 20 Jan 2023 14:43:18 GMT

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