BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
GingerJJ
Obsidian | Level 7

Hello,

 

I'm using SAS Viya and trying to import excel file into it.

I have a variable date1 as date in my excel file. The data looks like:

Date1

9/12/2024

9/25/2024

...

This is code(I used the drop down import function and the code is autogenerated):

FILENAME REFFILE DISK 'my routeXXX';
 
PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT1;
GETNAMES=YES;
RUN;
However, once imported  date1 shows up like:
45537
45560
...
 
It seems SAS turned it into number of days from day0 in the system.
 
Can anyone please help me?
Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

@PaigeMiller wrote:

How about that, the code marked correct had a typographical error.

 

This is the correct code

 

SAS_date = input(Excel_date,32.) + '30DEC1899'd ;
format SAS_date date9.;

 

 

@ChrisHemedinger can this be fixed on the old post at https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS...  ?


Fixed.

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

Excel dates are different than SAS dates when PROC IMPORT is used. In fact, they are off by 60 years and 1 day.

 

See 

https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/068-29.pdf

https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS...

--
Paige Miller
GingerJJ
Obsidian | Level 7

Thank you. I now understand the problem.

I tried the code in your second link:

 

SAS_date = input(Excel_date,32.) + '30DEC1899'x ;
format SAS_date date9.;

It gives me this error:

ERROR: Invalid hexadecimal constant string '30DEC1899'x.

Right now, the value under my imported Date1 variable is in excel unformatted format as character.

Any other tips?

PaigeMiller
Diamond | Level 26

How about that, the code marked correct had a typographical error.

 

This is the correct code

 

SAS_date = input(Excel_date,32.) + '30DEC1899'd ;
format SAS_date date9.;

 

 

@ChrisHemedinger can this be fixed on the old post at https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS...  ?

--
Paige Miller
Tom
Super User Tom
Super User

@PaigeMiller wrote:

How about that, the code marked correct had a typographical error.

 

This is the correct code

 

SAS_date = input(Excel_date,32.) + '30DEC1899'd ;
format SAS_date date9.;

 

 

@ChrisHemedinger can this be fixed on the old post at https://communities.sas.com/t5/SAS-Tips-from-the-Community/SAS-Tip-Conversion-from-Excel-Date-to-SAS...  ?


Fixed.

Tom
Super User Tom
Super User

You have at least one cell in that column of the spreadsheet that has a CHARACTER value instead of a DATE value.  That is why PROC IMPORT had to make the variable character instead of numeric.

 

If you can eliminate those character cells then PROC IMPORT should be able to make a numeric variable with appropriate DATE values and attach a date type format for displaying them in a human readable style.

SASKiwi
PROC Star

Applying an Excel Date format to the column in the spreadsheet prior to importing should avoid having to do any extra conversion in SAS. 

GingerJJ
Obsidian | Level 7

I did make sure to change it to Date in excel but it didn't help...

 

Tom
Super User Tom
Super User

@GingerJJ wrote:

I did make sure to change it to Date in excel but it didn't help...

 


Changing the display style used for the cells will not change the values in the cells.  You have to actually remove the character strings.  Normally I see that when there is an invalid date, like the 31NOV2024 or 29FEB2023.  But also when reading in CSV files generated by programs like R or Python that insert strings like NA for missing values.

ballardw
Super User

Another option is to save the XLSX as a CSV file and read that with a data step, best, or import the CSV.

Generally the Excel format applied to the date field will be the result in the CSV and SAS will recognize most of those. And in the case of the mixed numeric/character values the character date that would export from Excel as "9/25/2024" will also generally be read by SAS correctly. A corresponding date format is then typically applied by Proc Import.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 9 replies
  • 1825 views
  • 5 likes
  • 5 in conversation