BookmarkSubscribeRSS Feed
Divya_S123
Calcite | Level 5

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.

7 REPLIES 7
Divya_S123
Calcite | Level 5

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.

RamKumar
Fluorite | Level 6

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

Divya_S123
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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).

Kurt_Bremser
Super User

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.

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 709 views
  • 0 likes
  • 5 in conversation