Help using Base SAS procedures

SAS Infile: Choosing a specific sheet in excel

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

SAS Infile: Choosing a specific sheet in excel

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]


Accepted Solutions
Solution
‎02-05-2015 12:06 PM
Super User
Posts: 19,815

Re: SAS Infile: Choosing a specific sheet in excel

Posted in reply to finans_sas

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

View solution in original post


All Replies
Solution
‎02-05-2015 12:06 PM
Super User
Posts: 19,815

Re: SAS Infile: Choosing a specific sheet in excel

Posted in reply to finans_sas

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 1000 views
  • 0 likes
  • 2 in conversation