SAS 9.4 won't Import from New version of Excel

[mambrose]

My organization recently pushed out updates to Windows 10 and moved us to Office 365.  I have the 32-bit version of Office 365 installed.  I now find that SAS can't import Excel files.  I get an error message saying that it can't find the right driver.  From previous Q & A's I determined that I need to change the version of PC Files Server.  However, I now find that the SAS Deployment Center won't run.  I try running it; it starts and then seems to die.  Any ideas what to try next?

[ballardw]

Please show the code you attempted and any messages. Copy from the log and paste into a code box opened on the forum with the </> icon.

Sometimes you need to change things related to the source file. But what depends on how you try. So provide a few more details.

[mambrose]

PROC IMPORT OUT= City_trees_DBH 
        DATAFILE= "D:\Laptop_UFORE_Output\Street tree reports\California\City\treedata.xlsx" 
	 	DBMS=EXCELCS REPLACE;
     RANGE="DBH"; 
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

Error Message:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name
not found and no default driver specified
ERROR: Error in the LIBNAME statement.
ERROR: Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.98 seconds
cpu time 0.31 seconds

[JackHamilton]

Might be one of those 32- vs 64-bit problems with Office. We've had very similar messages when something was installed wrong, or not installed at all. Someone in our IT department had to spend a lot of time figuring out the correct drivers and configuration for our databases (and the ODBC driver makes Excel look like a database).

[Kurt_Bremser]

EXCELCS has to be used in conjunction with the PC Files Server, and needs server parameters to work (servername etc)

Since you have a XLSX file, make sure it is available for the SAS session and use DBMS=XLSX or LIBNAME XLSX to access the file. This engine is available on Windows and UNIX, does not rely on any MS-supplied modules, and does not suffer from the "Bitness Flu".

For further help, please tell us about your SAS architecture (client/server vs. local, operating system(s), user interface)

[mambrose]

I tried that, but SAS doesn't seem to be finding the sheet that I want to import in the Excel workbook. The revised code looks like:

PROC IMPORT OUT= city_DBH 
        DATAFILE= "D:\Laptop_UFORE_Output\Street tree reports\State\City.xlsx" 
	 	DBMS=XLSX REPLACE;
     RANGE="sheet$"; 
RUN;

[Kurt_Bremser]

I don't know if XLSX supports ranges,

 biut since you have a locally installed SAS on Windows, try thois:

proc import
  out= city_DBH 
  datafile= "D:\Laptop_UFORE_Output\Street tree reports\State\City.xlsx" 
  dbms=excel
  replace
;
range="'sheet$'"; /* note single quotes inside double quotes */
run;

(https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acpcref&docsetTarget=n0ms...)

Alternatively, use

libname in xlsx "D:\Laptop_UFORE_Output\Street tree reports\State\City.xlsx";

and then look at the library to see the sheet names as SAS sees them.

[mambrose]

I tried using the libname statement you suggested - no errors running it. Looking in the library, I don't see any sheets. The library looks empty even though the Excel workbook has 3 sheets in it.

[Kurt_Bremser]

Then the Excel workbook file is not (from the POV of the SAS session) where you expect it.

Since LIBNAME XLSX or EXCEL can be used to create a file, the presence of the file is not checked when the library is defined, and will only be checked for a valid path name once you write to the library. Up till then, the library is reported as empty if no file was found.

[mambrose]

So, I tried using PROC IMPORT with DBMS=XLSX and no RANGE statement, and it works IF I pull the sheet that I want to import out and save it in its own EXCEL workbook file. So, I have a workable temporary solution, but it won't work large scale because what I want to import are all EXCEL sheets that are derived from data in other sheets in the workbook. I guess the only long-term solution is to get PC Files Server to work again.

[mambrose]

Upon further testing, I found that PROC IMPORT with DBMS=XLSX will also work importing from a multi-sheet EXCEL workbook file IF the sheet I want to import is the first sheet in the file. So, I would still need to get PC Files Server running correctly to import more than one sheet from the same EXCEL file.

[Tom]

---

@mambrose wrote:
Upon further testing, I found that PROC IMPORT with DBMS=XLSX will also work importing from a multi-sheet EXCEL workbook file IF the sheet I want to import is the first sheet in the file. So, I would still need to get PC Files Server running correctly to import more than one sheet from the same EXCEL file.

---

PROC IMPORT will import the first sheet if you don't tell it which one you want.

You can use the SHEET= statement to tell it the name of the sheet you want.  Or add the sheetname to the value in the RANGE= statement.

proc import dbms=xlsx out=test1 replace
  datafile='c:\downloads\three.xlsx'
;
run;
proc import dbms=xlsx out=test2 replace
  datafile='c:\downloads\three.xlsx'
;
  sheet='Sheet2';
run;
proc import dbms=xlsx out=test3 replace
  datafile='c:\downloads\three.xlsx'
;
  range='$A1:0';
run;
proc import dbms=xlsx out=test4 replace
  datafile='c:\downloads\three.xlsx'
;
  range='Sheet2$A1:B2';
run;

[mambrose]

And I am running a local version of SAS 9.4 under Windows 10 (x64). It's Windows 10.0.1863 to be exact.

[JackHamilton]

Doesn't solve the problem directly, but can you use the XLSX libname engine instead?  I think it doesn't use the Microsoft drivers on Windows.