BookmarkSubscribeRSS Feed
seblg29
Calcite | Level 5

Hi Friends - I am using sas 9.0.

 

I would like to import my data from xlsx files, but I have troubles to do it...

Here is the error saying that :

 

ERREUR: Cannot specify both FILE= and TABLE=

 

Can someone help me to figure what's wring with my code please!!!

 

Thanks!

 

proc import
        datafile="C:\Users\Analysis_of_Covariance.xlsx"
        datatable="DATA"
        out=Ds0 replace
        DBMS=excel;

    run;

Thanking you in advance,
 
sebastien
6 REPLIES 6
LinusH
Tourmaline | Level 20
Please check the syntax Excel files. It's quite clear what options to use.
Data never sleeps
FreelanceReinh
Jade | Level 19

Hi Sebastien,

 

I can only partially agree with @LinusH: Yes, the syntax documentation of PROC IMPORT says "quite clearly" that the DATATABLE= option is "Not used for Microsoft Excel files." However, the specific documentation on "Microsoft Excel Workbook Files" does not seem very user-friendly to me. Depending on what "DATA" denotes in your case (a sheet or range name), you should use the SHEET= or RANGE= option instead of DATATABLE=.

 

(I can't test this because I don't have a SAS/ACCESS license.)

Kurt_Bremser
Super User

I also doubt that SAS 9.0 (!) can deal with xlsx files, as it was released long before the xlsx format was standardized and became the default file format for Excel with Office 2007.

seblg29
Calcite | Level 5

Hi all,

 

Thanks for your reply,

 

I try almost everything to import ma data : sheet= ; range= ...

I have still the same error message.

I think that the SAS 9.0 can't deal with xls or xlsx files.

 

If you have another idea, let me know.

 

Sebastien

FreelanceReinh
Jade | Level 19

The original error message "Cannot specify both FILE= and TABLE=" was, I think, due to the concurrent use of the DATAFILE= and DATATABLE= options of PROC IMPORT. So, it would be surprising to see "still the same error message" after you replaced DATATABLE= by, say, SHEET=. What does the log say exactly?

 

Have you tried the Import Wizard?

 

Other workarounds include saving the Excel files as CSV files and then importing these (using the Import Wizard, PROC IMPORT or a data step, the most flexible option). The technique I used most in the past (with SAS 6.12 and SAS 8.x) is DDE (Dynamic Data Exchange).

Kurt_Bremser
Super User

Standard solution: use a proper data transfer format. Save data from Excel as .CSV, and import that with a data step. Works across all SAS and all Excel versions, and you're free from the MS shenanigans. (changes in MS DAO from version to version, for ex)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2279 views
  • 8 likes
  • 4 in conversation