The SAS Output Delivery System and reporting techniques

Ways of importing excel file into SAS?

Reply
Contributor
Posts: 52

Ways of importing excel file into SAS?

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

Super User
Super User
Posts: 7,407

Re: Ways of importing excel file into SAS?

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

Super User
Posts: 9,682

Re: Ways of importing excel file into SAS?

1) proc import

2) libname

3) ODBC

4) DDE <-- old school

5) filename + clipboard

Contributor
Posts: 52

Re: Ways of importing excel file into SAS?


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.

Super User
Super User
Posts: 6,502

Re: Ways of importing excel file into SAS?

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;

Contributor
Posts: 52

Re: Ways of importing excel file into SAS?

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

Super User
Super User
Posts: 6,502

Re: Ways of importing excel file into SAS?

Upgrade.  Smiley Happy

Try changing to XLS and see if it works.

Other trick from the bad old days was to use

SHEET="'A B'$"

Ask a Question
Discussion stats
  • 6 replies
  • 751 views
  • 0 likes
  • 4 in conversation