topic Re: Cannot import excel to SAS in SAS Programming

Cannot import excel to SAS

Ecolife — Wed, 02 May 2018 13:06:37 GMT

Hi,

This is Kelly and is my first time to post my question here...

The problem is that I couldn't import my excel to SAS...and I have checked the version of excel and it is excel 2016 64bit(office 365) and my PC is also X64 processor.

The error still shows:" Connect: Class not registered. Error in the LIBNAME statement." after I tried most solutions...including installing PC server and SAS re-installation.

I have done the flowchart for troubleshooting: http://support.sas.com/kb/60/356.html#mdd-connect, and it says I should consult the technical support. Is there any further instruction for solving this problem? Or where should I ask for help?

Re: Cannot import excel to SAS

Vish33 — Wed, 02 May 2018 12:52:35 GMT

Hi Kelly,

Can you please post the code that you used for importing excel file.

Re: Cannot import excel to SAS

Ecolife — Wed, 02 May 2018 12:59:40 GMT

PROC IMPORT DATAFILE="d:\icd9.xlsx"
     DBMS=EXCELCS
     OUT=WORK.test REPLACE;
RUN;

I have tried EXCELCS and EXCEL but there are in the same result.

Re: Cannot import excel to SAS

Vish33 — Wed, 02 May 2018 13:06:21 GMT

have you tried with XLS or XLSX Replace options.

Re: Cannot import excel to SAS

emrancaan — Wed, 02 May 2018 13:15:22 GMT

options validvarname=any;
proc import out = multi 
	datafile = "test.xlsx" 
	dbms = xlsx  
	replace;    
	getnames = Yes; 
run;

Re: Cannot import excel to SAS

Ecolife — Wed, 02 May 2018 13:23:58 GMT

Thanks for your great help! It works since I changed DBMS=excel to xlsx... However, I still can't use import wizard to import my excel data...It seems that I should import them manually QQ

Re: Cannot import excel to SAS

Vish33 — Wed, 02 May 2018 13:32:32 GMT

You are welcome! code always should work across versions. Is importing through wizard is failing? in that case we have to see the excel versions its supporting through wizard.

Re: Cannot import excel to SAS

Ecolife — Wed, 02 May 2018 14:07:19 GMT

It still doesn't work with the import wizard. My SAS version is 9.4, and my excel version is 64-bit 2016 office365(provided from my school...)

Re: Cannot import excel to SAS

Ecolife — Wed, 02 May 2018 14:07:49 GMT

It's also a great solution. Thanks a lot!