Desktop productivity for business analysts and programmers

ERREUR: Cannot specify both FILE= and TABLE=

Reply
New Contributor
Posts: 2

ERREUR: Cannot specify both FILE= and TABLE=

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
Esteemed Advisor
Posts: 5,198

Re: ERREUR: Cannot specify both FILE= and TABLE=

Please check the syntax Excel files. It's quite clear what options to use.
Data never sleeps
Trusted Advisor
Posts: 1,114

Re: ERREUR: Cannot specify both FILE= and TABLE=

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.)

Esteemed Advisor
Posts: 6,646

Re: ERREUR: Cannot specify both FILE= and TABLE=

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 2

Re: ERREUR: Cannot specify both FILE= and TABLE=

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

Trusted Advisor
Posts: 1,114

Re: ERREUR: Cannot specify both FILE= and TABLE=

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).

Esteemed Advisor
Posts: 6,646

Re: ERREUR: Cannot specify both FILE= and TABLE=

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 6 replies
  • 487 views
  • 1 like
  • 4 in conversation