topic Re: error when reading excel97 files on win10 and sas 9.4 in SAS Programming

error when reading excel97 files on win10 and sas 9.4

emaguin — Wed, 30 Sep 2020 15:02:05 GMT

I'm not sure this is the right place but perhaps the right place can be pointed out.

Here's the problem. I have a win10 machine, sas9.4, and Office2019, all are 64 bit. I want read excel97 data files and i get an error:

ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.

See attached file for complete syntax. A co-worker googled something related to the "ERROR: Connect ..." statement and found this website

https://support.sas.com/kb/43/933.html

This link references sas 9.3 and Office 2010, 2013, and 2016.

Is this information current with respect to my machine and installed software?

Re: error when reading excel97 files on win10 and sas 9.4

CurtisMackWSIPP — Wed, 30 Sep 2020 15:06:07 GMT

I believe the EXCEL dbms can only handle XLSX files. Try the XLS dbms.

Re: error when reading excel97 files on win10 and sas 9.4

emaguin — Wed, 30 Sep 2020 16:56:02 GMT

Respectfully, I converted an xls file to xlsx using Office 2019 and the described syntax would not read the xlsx file. And, where would a person find an installable version of excel 97 that would run on a win10 x64 machine. The alternative is to open and resave the files as csv, but we have, I guess, 3 waves * 4 respondents * 3 interview parts * 15 xls files per part.

Re: error when reading excel97 files on win10 and sas 9.4

Tom — Wed, 30 Sep 2020 17:32:09 GMT

Did you try using DBMS=XLS instead of DBMS=EXCEL?

Re: error when reading excel97 files on win10 and sas 9.4

emaguin — Wed, 30 Sep 2020 17:46:13 GMT

No, we didn't try that but I did just now. Here's what came back (but maybe there are some other things that need to be changed because this syntax was likely written circa 2010):
5 PROC IMPORT OUT= work.peer1lt1
6 DATAFILE= "N:\UBNeighborhoodFDP\UB Neighborhood Data\DATA\Data\T3 sas
6 ! data\Child\Questionnaires\Peer\t3_peer_interview_part_1_lt1.xls"
7 DBMS=xls REPLACE;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
8 SHEET="t3_peer_interview_part_1_lt1$";
NOTE: The previous statement has been deleted.
9 GETNAMES=YES;
10 MIXED=YES;
11 SCANTEXT=YES;
--------
180
12 USEDATE=YES;
-------
180
13 SCANTIME=YES;
--------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

14 RUN;

Re: error when reading excel97 files on win10 and sas 9.4

ghosh — Wed, 30 Sep 2020 17:46:25 GMT

I have no issues importing excel97 file. Instead of libname, use filename to point to the file

FILENAME dat '~/dat/class.xls';
PROC IMPORT DATAFILE=dat
DBMS=XLS replace
OUT=WORK.IMPORT;
GETNAMES=YES; 
SHEET="All Students"n;
RUN;

Re: error when reading excel97 files on win10 and sas 9.4

emaguin — Wed, 30 Sep 2020 18:03:13 GMT

Ok. I cleared the libname. Here's the log. I don't know why the Scantext, Usedate, and Scantime subcommands are used but I assume they are there because they were needed or are needed. Also, what does the "n" following the sheetname do? That wasn't liked either.
Separately from this, I went through the proc import menu system and I got the original Connect error.

17
18 FILENAME dat "N:\UBNeighborhoodFDP\UB Neighborhood Data\DATA\Data\T3 sas data\
19 Child\Questionnaires\Peer\t3_peer_interview_part_1_lt1.xls";
20 PROC IMPORT DATAFILE=dat
21 DBMS=XLS replace
22 OUT=work.peer1lt1;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
23 GETNAMES=YES;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
24 MIXED=YES;
25 SCANTEXT=YES;
--------
180
26 USEDATE=YES;
-------
180
27 SCANTIME=YES;
--------
180
28 SHEET="t3_peer_interview_part_1_lt1$";n;
-
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

29 RUN;

NOTE: The SAS System stopped processing this step because of errors.

Re: error when reading excel97 files on win10 and sas 9.4

ghosh — Wed, 30 Sep 2020 18:17:44 GMT

n follows a non-standard file name or worksheet name, for example where there are embedded spaces. It should be inside the semi-colon not outside. Yours has none so it's not needed

Try running my code with the same options. Then check the whether you other options are applicable in the xls engine (perhaps Excel has been deprecated, it does not work in SAS UE)

Please post your code (not as a log) and a sample of your data (dummy it up if confidential).

Re: error when reading excel97 files on win10 and sas 9.4

SASKiwi — Wed, 30 Sep 2020 20:40:17 GMT

SCANTEXT, USEDATE and SCANTIME are not valid statements when you use DBMS = XLS.

Check this link for the valid statements and options.

Re: error when reading excel97 files on win10 and sas 9.4

emaguin — Wed, 30 Sep 2020 21:35:13 GMT

The denoument: one of our IT persons installed Microsoft access runtime 2013. Problem is now fixed.
I want to thank the persons who responded.
Gene Maguin

Re: error when reading excel97 files on win10 and sas 9.4

SASKiwi — Wed, 30 Sep 2020 21:42:27 GMT

@emaguin - Please mark your post as answered in that case.