open excel data in sas using infile statement

[raheleh22]

Hi, 

I am having hard time opening a data using infile statement. here is my code and I do not know why it is giving the error of "0" 0bservations! I know how to do it with proc import but I want to find out why infile statement is not working. 

DATA example;
INFILE 'Location\example.xlsx' DSD FIRSTOBS=2; /* first obesrvation will be second row*/
INPUT patient encounter smoking weight bloodpressure;
RUN;
PROC PRINT DATA=example;
RUN;

Thanks a lot, 

[PaigeMiller]

Since Excel files are not really the same as plain text files, trying to read them with a data step as if they were plain text files will almost definitely not give the desired results. What happens if you use PROC IMPORT instead?

proc import datafile="Location\example.xlsx" dbms=excel out=example;
     getnames=YES;
run;

[raheleh22]

I am able to get results with proc import. I was just wondering why infile statement is not working. thanks for your response.

[Kurt_Bremser · Accepted solution]

INFILE in combination with INPUT is mainly for reading text files (which are the main vehicle for the transfer of data in professional environments).

Excel (.xslx) files are ZIP-compressed archives of multiple XML files which contain and describe the data.

If you want to use something other than PROC IMPORT or LIBNAME XLSX, you need to use a fileref of type ZIP, read the members (the .xml files) separately and parse the XML.