topic Re: open excel data in sas using infile statement in New SAS User

open excel data in sas using infile statement

raheleh22 — Mon, 28 Aug 2023 11:20:00 GMT

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,

Re: open excel data in sas using infile statement

PaigeMiller — Mon, 28 Aug 2023 11:34:35 GMT

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;

Re: open excel data in sas using infile statement

raheleh22 — Mon, 28 Aug 2023 11:58:46 GMT

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

Re: open excel data in sas using infile statement

Kurt_Bremser — Mon, 28 Aug 2023 14:57:22 GMT

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.