CODEWITHSUNDEEP
excelimportsasproc
NuValue
NuValue

Reputation: 463

Import file from Excel into SAS Error Meesage

I am trying to import a 2007 MS Excel into SAS. I use SAS 9.2. with the Acces module for PC file already installed. I used the next code:

 proc import datafile = 'C:\saspractica\Excel.xlsx' out= work.auto1 dbms = excel REPLACE;
 sheet = 'auto';
 GETNAMES = YES;
 run;

Nevertheless, SAS shows me the next message:

ERROR: Connect: external table does not have expected format. ERROR: Error in the LIBNAME statement. 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.21 seconds cpu time 0.14 seconds

I have checked the code and changed dbms = xlsx to dbms=excel. However, the error message continues... Thanks for your time and help.

Upvotes: 0

Views: 1231

Answers (1)

Reeza
Reeza

Reputation: 21264

Assuming you have SAS 9.2 (TS1M2) or greater you can try something like the following using the libname method. This will work on a Windows machine, but not a Unix one.

Set up a library that references your excel file like below:

libname datain excel 'C:\saspractica\Excel.xlsx';

Then navigate to the explorer window and open the library to view the sheet and sheet names. If the sheet name is auto try the following:

data want;
set data.auto;
run;

If the sheet name is auto$ try the following instead:

data want;
set data.'auto$'n;
run;

Close the connection to the library/file when you finish the import

libname datain;

Upvotes: 0

Related Questions