Ways of importing excel file into SAS?

[dkanand86]

Hi Everyone,

Please can you suggest 4-5 ways of importing excel file into SAS 9.3

Note:

1. When i use proc import, SAS gives error related invalid dbms= option.

Please help.

Thanks

[RW9]

Note: - Use a valid dbms option?  I.e. do you have SAS Access installed, are you using the correct dbms for you file type, here is the list of them:

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003102096.htm

That aside, I wouldn't recommend using:

a) Excel - this is not a good data transfer format.  Use CSV, XML or any of the other proper structured data transfer formats

b) Proc import/export - these are guessing procedures.  They guess what data you want to import.  Use a proper data transfer format and write and explicit datastep import to import the data as you know it to be.

Options:

1) Datastep infile and inputs with CSV file.  Most robust method.

2) libname xml

3) libname excel - limited to accessible data

4) proc import - guesswork

[Ksharp]

1) proc import

2) libname

3) ODBC

4) DDE <-- old school

5) filename + clipboard

[dkanand86]

Please can you provide the exact code for importing using datastep and importing using proc import.

I will definitely look into the list of dbms types from the link you provided.

[Tom]

If you have to do it use XLSX file format and DBMS=XLSX.

To just get the first sheet use:

proc import file='myfile.xlsx' out=MYSAS dbms=xlsx;

run;

[dkanand86]

my SAS shows, it is dbms= type is INVALID.

[Tom]

Upgrade. 

Try changing to XLS and see if it works.

Other trick from the bad old days was to use

SHEET="'A B'$"