SAS Infile: Choosing a specific sheet in excel

finans_sas — Thu, 05 Feb 2015 16:59:30 GMT

Hi Everyone,

I am trying to read a few hundred excel files (.xlsx) into SAS. Below is the code that I have put together from various sources (mostly UCLA). Each excel file has the same column names, but the length of the strings in the columns change from one excel file to another. The challenge is that each excel file has two sheets, and data is located in "Data Sheet". I have been trying to figure out where to put a statement like "sheet="Data Sheet" in the code, but I could not figure out where. (Note: I also have a macro code that imports all this data using PROC IMPORT, but truncation is such a big problem that I am now using the code below). I look forward to hearing your suggestions.

Thanks!

%let dirname = _____________;

filename DIRLIST pipe "dir /B &dirname\*.xlsx";

data dirlist ;

length fname $256;

infile dirlist length=reclen ;

input fname $varying256. reclen ;

run;

proc print data = dirlist;

run;

data all_cint (drop=fname);

length myfilename $50;

set dirlist;

filepath = "&dirname\"||fname;

infile dummy filevar = filepath length=reclen end=done missover firstobs=2 dlm=',' dsd lrecl=32767 ;

do while(not done);

myfilename = filepath;

[followed by input names]

Re: SAS Infile: Choosing a specific sheet in excel

Reeza — Thu, 05 Feb 2015 17:06:20 GMT

Have you looked into DBSASTYPE option with a libname method of importing?

topic Re: SAS Infile: Choosing a specific sheet in excel in SAS Procedures

SAS Infile: Choosing a specific sheet in excel

Re: SAS Infile: Choosing a specific sheet in excel