topic Re: SAS 9.4 won't Import from New version of Excel in Administration and Deployment

SAS 9.4 won't Import from New version of Excel

mambrose — Fri, 23 Oct 2020 21:08:03 GMT

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?

Re: SAS 9.4 won't Import from New version of Excel

ballardw — Fri, 23 Oct 2020 21:26:33 GMT

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.

Re: SAS 9.4 won't Import from New version of Excel

JackHamilton — Fri, 23 Oct 2020 21:29:56 GMT

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.

Re: SAS 9.4 won't Import from New version of Excel

mambrose — Fri, 23 Oct 2020 21:40:00 GMT

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

Re: SAS 9.4 won't Import from New version of Excel

JackHamilton — Fri, 23 Oct 2020 22:01:29 GMT

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

Re: SAS 9.4 won't Import from New version of Excel

Kurt_Bremser — Tue, 27 Oct 2020 08:52:08 GMT

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)

Re: SAS 9.4 won't Import from New version of Excel

mambrose — Tue, 27 Oct 2020 14:01:57 GMT

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;

Re: SAS 9.4 won't Import from New version of Excel

mambrose — Tue, 27 Oct 2020 14:12:32 GMT

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

Re: SAS 9.4 won't Import from New version of Excel

Kurt_Bremser — Tue, 27 Oct 2020 14:31:40 GMT

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=n0msy4hy1so0ren1acm90iijxn8j.htm&locale=en)

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.

Re: SAS 9.4 won't Import from New version of Excel

mambrose — Tue, 27 Oct 2020 14:57:25 GMT

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.

Re: SAS 9.4 won't Import from New version of Excel

mambrose — Tue, 27 Oct 2020 15:12:06 GMT

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.

Re: SAS 9.4 won't Import from New version of Excel

Kurt_Bremser — Tue, 27 Oct 2020 15:15:27 GMT

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.

Re: SAS 9.4 won't Import from New version of Excel

mambrose — Tue, 27 Oct 2020 15:30:59 GMT

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.

Re: SAS 9.4 won't Import from New version of Excel

Tom — Tue, 27 Oct 2020 16:34:01 GMT

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