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 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;
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?
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!
@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;
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.;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.