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):
@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.
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
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?
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... ?
@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.
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.
Applying an Excel Date format to the column in the spreadsheet prior to importing should avoid having to do any extra conversion in SAS.
I did make sure to change it to Date in excel but it didn't help...
@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.
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.
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.
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.