topic Re: Need to read several date columns from excel file into SAS in SAS Programming

Need to read several date columns from excel file into SAS

SAS_1001 — Tue, 12 Mar 2024 18:26:46 GMT

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

Re: Need to read several date columns from excel file into SAS

ballardw — Tue, 12 Mar 2024 18:50:42 GMT

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

Re: Need to read several date columns from excel file into SAS

SAS_1001 — Tue, 12 Mar 2024 20:02:25 GMT

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.

Re: Need to read several date columns from excel file into SAS

Kurt_Bremser — Tue, 12 Mar 2024 20:08:22 GMT

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.

Re: Need to read several date columns from excel file into SAS

Sajid01 — Tue, 12 Mar 2024 20:44:20 GMT

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.

Re: Need to read several date columns from excel file into SAS

Tom — Tue, 12 Mar 2024 21:11:43 GMT

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

Re: Need to read several date columns from excel file into SAS

SAS_1001 — Tue, 12 Mar 2024 21:51:24 GMT

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

Re: Need to read several date columns from excel file into SAS

Tom — Tue, 12 Mar 2024 22:08:07 GMT

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

Re: Need to read several date columns from excel file into SAS

Sajid01 — Wed, 13 Mar 2024 13:21:29 GMT

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 .