Proc Import with Excel File Dates Wrong

[erin3]

Hi! When I import this excel file, the dates come in as a random set of numbers that aren't in date format or correct. Below is the import code I'm using. Can someone please correct me to get them to import as dates? MMddyy10. is preferred. 

proc import 
out=work.vax
datafile="\\myfile\Test_11OCT2021.xlsx"
dbms=xlsx replace;
getnames=yes;
run;

Thanks!

[erin3]

(A sample of my data is attached)

[PaigeMiller]

Some of us refuse to download Microsoft Office files because they are security threats. You can include a portion of the data in your reply as a SAS data step code (instructions)

But please, show us what you see when you say "the dates come in as a random set of numbers that aren't in date format or correct". What happens if you assign a format to these values in SAS? Do they look correct then?

[erin3]

Oh, I had no idea! Okay, here is the data from the spreadsheet: 

id	Date of Birth
1	03/19/1998
2	04/15/2004
3	02/20/1986
4	-
5	10/11/1995
6	04/29/1978
7	05/09/1998
8	09/01/1972
9	10/28/1998
10	09/21/1998
11	10/10/1990
12	09/05/1995

Then the dates show up this way in SAS. 

Any thoughts? 

Many thanks!

[Patrick · Accepted solution]

@erin3 Some of us do download Excels so no hurt to still attach it.

As soon as you've got a character in a cell (like in row 4) SAS will read the data as character - and use the internal value for the dates stored as a character string.

Below one way how to convert these strings from Excel to a SAS Date value.

options validvarname=v7;

proc import 
  out=work.vax
  datafile="c:\temp\Test_11OCT2021.xlsx"
  dbms=xlsx replace;
  getnames=yes;
run;


data want;
  set vax;
  format DoB date9.;
  DoB=intnx('year',input(date_of__Birth,?? best32.),-60,'s')-1;
run;

[Tom]

Actually the digit strings you see in the character variable represent the number that EXCEL uses to represent that date, not the number SAS would use to represent the same date.

In most cases EXCEL uses 1900 as the basis date instead of 1960 like SAS.  Do you just need to add the SAS date '30DEC1899'd to the value to adjust for this difference.  You will end up with a smaller number since 1900 is before 1960 so its date values are negative.  The reason to use Dec 30th instead of Jan 1 is because of difference in whether to count from zero or one and Excel treats 1900 as a leap year (which it was not).

  DoB=input(date_of__Birth,?? 32.) + '30DEC1899'd ;
  format DoB date9.;