topic Re: Hi, in SAS Programming

Hi,

Divya_S123 — Fri, 19 Dec 2014 07:09:19 GMT

Hi,

Am trying to import Excel file using INFILE Statement. Can anybody help me by providing the details, I do not want to use IMPORT or DDE.

Re: Hi,

RamKumar — Fri, 19 Dec 2014 09:25:38 GMT

Divya,

Look at a example 3.3 in http://www.ats.ucla.edu/stat/sas/library/SASRead_os.htm

Also look at SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Re: Hi,

Divya_S123 — Fri, 19 Dec 2014 09:38:13 GMT

Thanks for the reply RamKumar,

In Example 3.3 which u mentioned reading a txt file is given however, I want to read xls file using INFILE statement.

Re: Hi,

RamKumar — Fri, 19 Dec 2014 09:49:34 GMT

You can follow the same example by replacing .txt with .csv

Re: Hi,

Divya_S123 — Fri, 19 Dec 2014 09:53:57 GMT

Ram actually i don't want to convert xls file into CSV file, I want to read xls directly without converting it to csv file using INFILE Statement

Re: Hi,

RW9 — Fri, 19 Dec 2014 09:56:23 GMT

Hi,

You need to clarify your question. Is the file you are dealing with:

XLS - I.e. Excel proprietary binary file (previous Excel version < 2007 have this a default) - This type of file cannot be read by methods other than proc import as it uses the JET engine.

XSLX - Excel native XML zipped file (current releases of Excel use this as standard now) - In theory you can unzip and read the individual XML files, not recommended though.

CSV - Comma Separated Variable file - this is a text file which can be parsed by Excel - Easy to read in an parse

Other - XML, text etc. - Easy to read in and parse.

Depending on the file type your code will change. Also bear in mind that using infile within a datastep and writing the import yourself is the preferred method, however you would be better served getting the file to CSV first (save as CSV in Excel).

Re: Hi,

Kurt_Bremser — Fri, 19 Dec 2014 14:26:33 GMT

What is your environment? Especially, on what platform is your base SAS running?

Is SAS/ACCESS to PC Files licensed?

You cannot use the INFILE statement of a data step directly for a .xls file, because Excel files are not structured in rows and colums on a physical level. Just a stream of bytes that needs to be decoded.

Even the import wizard in SAS Enterprise Guide does not read the .xls on its own, it relies on a piece of MS SW that does the decoding.

Do a web search for "Import Excel into SAS", there are tons of resources covering multitudes of methods, all having their pro's and con's.

Re: Hi,

art297 — Fri, 19 Dec 2014 14:31:38 GMT

You haven't mentioned why you want to use an input statement. An alternative way of reading Excel files is the libname access method. With it, you can do most of the things you'd typically like to accomplish with an input statement. Here are two links that describe it;

SAS/ACCESS(R) 9.3 Interface to PC Files: Reference

and

http://www2.sas.com/proceedings/sugi31/024-31.pdf