topic Cannot import excel file (xls) into SAS 9.4 version in window 10 environment in SAS Programming

Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

joon1 — Sat, 26 Sep 2020 20:31:35 GMT

Dear Madam/Sir,

I have recently changed window environment from 7 to 10. When the excel file is imported into SAS 9.4 using import menu, import cannnot be done and

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 searched this community blogs and found the following code and have error message.

3 PROC IMPORT DATAFILE="C:\Users\hakjoon\Documents\Labor union and
3 ! comparability\data\Unionizationrate19992018"
4 DBMS=EXCELCS
5 OUT=WORK.e1 REPLACE;
6 RUN;

ERROR: Unable to open file C:\Users\hakjoon\Documents\Labor union and
comparability\data\Unionizationrate19992018. It does not exist or it is already opened
exclusively by another user, or you need permission to view its data.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.33 seconds
cpu time 0.04 seconds

Your help will be highly appreciated.

Thanks

Joon1

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

Kurt_Bremser — Sat, 26 Sep 2020 21:59:06 GMT

Excel files should have a file extension of xls or xlsx, your filename has no extension.

For a modern xlsx file, DBMS=XLSX or LIBNAME XLSX should work, regardless of bitness.

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

joon1 — Sun, 27 Sep 2020 02:03:32 GMT

Dear KurtBremser,

The following is the error message without extension in the file name "Unionizationrate19992018".

11 proc import DATAFILE="C:\Users\hakjoon\Documents\Labor union and
11 ! comparability\data\Unionizationrate19992018"
12 DBMS=xlsx
13 OUT=WORK.e1 REPLACE;
14 RUN;

ERROR: Physical file does not exist, C:\Users\hakjoon\Documents\Labor union and
comparability\data\Unionizationrate19992018.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

The following is the error message with extension in the file name "Unionizationrate19992018.xls".

15 proc import DATAFILE="C:\Users\hakjoon\Documents\Labor union and
15 ! comparability\data\Unionizationrate19992018.xls"
16 DBMS=xlsx
17 OUT=WORK.e1 REPLACE;
18 RUN;

ERROR: Error opening XLSX file -> C:\Users\hakjoon\Documents\Labor union and
comparability\data\Unionizationrate19992018.xls.xlsx . It is either not an Excel spreadsheet or
it is damaged. Error code=8014900A
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Thank you for your help

Joon1

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

Kurt_Bremser — Sun, 27 Sep 2020 02:12:19 GMT

Your filename in the DATAFILE= still does not have an extension. Excel filenames end with .xlsx (or .xls for older files).

You hsve to supply the whole filename to the option.

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

Kurt_Bremser — Sun, 27 Sep 2020 02:14:47 GMT

If you have an older Excel file, I suggest you open it with Excel first and save it as xlsx.

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

jimbarbour — Sun, 27 Sep 2020 02:43:35 GMT

@joon1,

One thing you might try is changing EXCELCS to EXCEL for your DBMS parameter. That's a bit of a long shot, but it's worth a try. If that doesn't work, then as @Kurt_Bremser suggested, you could just open the Excel spreadsheet with Excel and save it as type xlsx. Then you could use the XLSX Libname engine. See example below.

PROC IMPORT DATAFILE="C:\Users\hakjoon\Documents\Labor union and comparability\data\Unionizationrate19992018.xls"
    DBMS=EXCEL
    OUT=WORK.e1 REPLACE;
RUN;

XLSX Libname example.

The sheet name inside the Excel file becomes the dataset name used with the SET statement.

LIBNAME MyXL XLSX "C:\Users\hakjoon\Documents\Labor union and comparability\data\Unionizationrate19992018.xls";

DATA  WORK.e1;
    SET MyXL.MySheet;
RUN;

Jim

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

joon1 — Sun, 27 Sep 2020 04:53:17 GMT

Thank you so much for your help. It is resolved!

Have a good weekend and stay safe.

Joon1

Re: Cannot import excel file (xls) into SAS 9.4 version in window 10 environment

marcel — Sun, 27 Sep 2020 22:22:27 GMT

Hi,

you could save your excel files as "xx.csv" files and that should give no problem at all.

At the sas interface click on the pull down 'file" menu, choose "import data" and navigate to the location of your csv file. Follow the indicated next steps from the import wizard pop-up menu and the importing of the file will go smoothly.