Need to read several date columns from excel file into SAS

[SAS_1001]

Trying to read several date columns (100+) from excel file into SAS. Dates are coming as a number but the idea is to read them exactly as they reflect below in the SAS Dataset.  The platform is: SAS Studio on Linux Platform.  Any help would be greatly appreciated.

1/5/2022

1/12/2022

1/19/2022

1/26/2022

2/2/2022

2/9/2022

2/16/2022

2/23/2022

3/2/2022

3/9/2022

3/16/2022

3/23/2022

3/30/2022

4/6/2022

4/13/2022

4/20/2022

4/27/2022

[ballardw]

What exactly do you mean by "read them exactly as they reflect below"?

Are those values in Excel strings or dates with the mm/dd/yyyy cell appearance added as a property?

What values are you getting in SAS that you don't like?

FWIW, dates in SAS are numbers whose appearance for humans to think they represent a date are controlled by a format. Change the format and the appearance changes but the underlying number doesn't.

If SAS is displaying values in a manner you don't like you can create custom formats that will show just about any typical date appearance (and few some consider odd like Julian dates).

[SAS_1001]

Columns should be read as dates and populate them exactly as they are but not get translated into numbers.  The options that I use in PC SAS with the below syntax works:

PROC IMPORT OUT= WORK.zz
DATAFILE= "Y:\folder1\sdm\customs2.xlsx"
DBMS=EXCEL REPLACE;
RANGE="'Scheduling Table$'";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

However, when I ran the same syntax in SAS Studio in Linux, it's not working with the following error:

69 PROC IMPORT OUT= WORK.zz

70 DATAFILE= "/home/mnt/dts-04.xls"

71 DBMS=EXCEL REPLACE;

ERROR: DBMS type EXCEL not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

[Kurt_Bremser]

Old Excel files (.xls) can only be read on Windows or (from any platform) by using the (also Windows-only) PC Files Server.

On UNIX, you need the newer .xlsx files, which can be read with DBMS=XLSX.

[Sajid01]

Hello @SAS_1001 
As advised by @Kurt_Bremser try changing the DBMS to xlsx.
I tried your code after changing DBMS=XLSX and everything is as you wanted.

[SAS_1001]

well, it's working in PC SAS but in SAS Studio its giving numbers.  Tried other solutions as well but no luck yet.

[Tom]

---

@SAS_1001 wrote:

well, it's working in PC SAS but in SAS Studio its giving numbers.  Tried other solutions as well but no luck yet.

---

Well PC-SAS is running on a PC.  So it is possible for the EXCEL engine, which calls code installed by EXCEL, to work.  But it sounds like your SAS/Studio session is connected to a copy of SAS that is not running on Windows.  In that case you need to use the XLSX engine.  (or in the case of your second code block the XLS engine)

But I would be surprised if it really treated a column with mixed DATE and CHARACTER cells any differently with the XLSX engine than it does with the EXCEL engine.

[Sajid01]

Hello @SAS_1001 

"well, it's working in PC SAS but in SAS Studio its giving numbers.  Tried other solutions as well but no luck yet."
I tested the code in SAS Studio itself - SAS on Demand for Academics and posted the results.
I repeat what you need to do  - use dbms=XLSX .

[Tom]

---

@SAS_1001 wrote:

Trying to read several date columns (100+) from excel file into SAS. Dates are coming as a number but the idea is to read them exactly as they reflect below in the SAS Dataset.  The platform is: SAS Studio on Linux Platform.  Any help would be greatly appreciated.

1/5/2022

1/12/2022

1/19/2022

1/26/2022

2/2/2022

2/9/2022

2/16/2022

2/23/2022

3/2/2022

3/9/2022

3/16/2022

3/23/2022

3/30/2022

4/6/2022

4/13/2022

4/20/2022

4/27/2022

---

Dates ARE numbers.

Please explain in more detail what is not working?

Note that if the COLUMN in the WORKSHEET has mixed numeric and character CELLS then the VARIABLE in the resulting SAS dataset will have to be CHARACTER (you can represent a number as a string of digits but you cannot represent a string of letters as a number).

In that case SAS will store the digit string that represents the number that EXCEL uses to represent that date.  You can convert it to a SAS date value by first converting it back into a number and then adjusting the value for the difference in how the two systems start counting.

So if you end up with a dataset named HAVE with a character variable named CHARSTRING you can use a step like this to make a dataset named WANT that adds a numeric variable named DATE that has date values.  Then attach whatever format you want so that the date value prints in a way that is easy for humans to read.

data want;
  set have;
  date = input(charstring,32.) + '30DEC1988'd;
  format date date9.
run;