topic Re: Convert Char to Date in SAS Viya in New SAS User

Convert Char to Date in SAS Viya

GingerJJ — Wed, 08 Jan 2025 18:00:51 GMT

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.

Re: Convert Char to Date in SAS Viya

PaigeMiller — Wed, 08 Jan 2025 18:13:16 GMT

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-Date/m-p/781804#M205

Re: Convert Char to Date in SAS Viya

GingerJJ — Wed, 08 Jan 2025 19:16:15 GMT

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?

Re: Convert Char to Date in SAS Viya

PaigeMiller — Wed, 08 Jan 2025 19:50:35 GMT

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

Re: Convert Char to Date in SAS Viya

Tom — Wed, 08 Jan 2025 20:29:40 GMT

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.

Re: Convert Char to Date in SAS Viya

Tom — Wed, 08 Jan 2025 20:31:52 GMT

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

Re: Convert Char to Date in SAS Viya

SASKiwi — Wed, 08 Jan 2025 20:48:07 GMT

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

Re: Convert Char to Date in SAS Viya

GingerJJ — Wed, 08 Jan 2025 20:54:08 GMT

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

Re: Convert Char to Date in SAS Viya

Tom — Wed, 08 Jan 2025 20:57:15 GMT

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

Re: Convert Char to Date in SAS Viya

ballardw — Thu, 09 Jan 2025 00:46:58 GMT

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.