BookmarkSubscribeRSS Feed
mambrose
Calcite | Level 5

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?

13 REPLIES 13
ballardw
Super User

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
Calcite | Level 5
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
Lapis Lazuli | Level 10
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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
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
Super User

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
Calcite | Level 5
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
Calcite | Level 5
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
Super User Tom
Super User

@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
Calcite | Level 5
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
Lapis Lazuli | Level 10

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.

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

Get Started with SAS Information Catalog in SAS Viya

SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 2787 views
  • 2 likes
  • 5 in conversation