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.
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
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.
You can follow the same example by replacing .txt with .csv
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
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).
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.