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,997

Re: Ways of importing excel file into SAS?

Posted in reply to dkanand86

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: 10,046

Re: Ways of importing excel file into SAS?

Posted in reply to dkanand86

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?

Posted in reply to 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.

Super User
Super User
Posts: 7,081

Re: Ways of importing excel file into SAS?

Posted in reply to dkanand86

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: 7,081

Re: Ways of importing excel file into SAS?

Posted in reply to dkanand86

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
  • 809 views
  • 0 likes
  • 4 in conversation