ERREUR: Cannot specify both FILE= and TABLE=

[seblg29]

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

[seblg29]

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;

[seblg29]

Thanking you in advance,

[seblg29]

sebastien

[LinusH]

Please check the syntax Excel files. It's quite clear what options to use.

[FreelanceReinh]

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]

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]

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]

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]

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)