Import file from Excel into SAS Error Meesage

0
votes

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.

1
excelimportsasproc
Do you have SAS 9.2 TS1M2 or greater? Before this release you can't import .xlsx files. – Reeza

1 Answers

0
votes

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;