topic Re: Ways of importing excel file into SAS? in ODS and Base Reporting

Ways of importing excel file into SAS?

dkanand86 — Fri, 17 Jul 2015 04:31:55 GMT

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

Re: Ways of importing excel file into SAS?

RW9 — Fri, 17 Jul 2015 08:57:32 GMT

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

Re: Ways of importing excel file into SAS?

Ksharp — Fri, 17 Jul 2015 13:51:47 GMT

1) proc import

2) libname

3) ODBC

4) DDE <-- old school

5) filename + clipboard

Re: Ways of importing excel file into SAS?

dkanand86 — Fri, 17 Jul 2015 16:10:37 GMT

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.

Re: Ways of importing excel file into SAS?

Tom — Fri, 17 Jul 2015 17:31:33 GMT

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;

Re: Ways of importing excel file into SAS?

dkanand86 — Fri, 17 Jul 2015 19:03:44 GMT

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

Re: Ways of importing excel file into SAS?

Tom — Fri, 17 Jul 2015 20:15:47 GMT

Upgrade.

Try changing to XLS and see if it works.

Other trick from the bad old days was to use

SHEET="'A B'$"