Help using Base SAS procedures

Reading Excel Files by Selecting Certain Sheets

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Reading Excel Files by Selecting Certain Sheets

Hi Everyone,

I have a very large number of excel files that I need to put in SAS. My files are in XLSX extension. These files come with two sheets. So far I opened each xlsx document and save the sheet with the data as csv. Next, I use the following code (thanks to UCLA) to read these CSV files. Even though this process works, it takes a long time to open each xlsx and save it as csv. I would appreciate your help with modifying the code below so that it reads XLSX files but only the sheet called "Data Sheet" (Note: the changes that I made are in green).

Thank you.

%let dirname = C:\sasfiles;

      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_excel (drop=fname);

  length myfilename $50;

  set dirlist;

  filepath = "&dirname\"||fname;

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

  do while(not done);

    myfilename = filepath;

input var1 var2 var3;

output;

run;


Accepted Solutions
Solution
‎01-08-2015 05:17 PM
Super User
Posts: 17,912

Re: Reading Excel Files by Selecting Certain Sheets

No, you can't read all excel files in the same way unfortunately.

You will need to either batch convert all CSV to XLSX or batch import. Either way, if using SAS a macro is a decent way to go.

I'm assuming all the files have the same structure though. Is the sheet name important to keep as well?

This is a often asked question so there are several solutions on here already that search will turn up.

View solution in original post


All Replies
Super User
Posts: 17,912

Re: Reading Excel Files by Selecting Certain Sheets

Why can't you read the Excel file directly? Do you not have access to SAS/ACCESS? Or is there some specific format in your Excel file that SAS can't read in properly?

Contributor
Posts: 55

Re: Reading Excel Files by Selecting Certain Sheets

Hi Reeza,

Thank you for your answer. I have about 1200 xlsx files. The code above works so well for csv files. I thought it would be great to tweak it so that SAS can read xlsx extension files as efficiently. I hope I was able to answer your question.

Solution
‎01-08-2015 05:17 PM
Super User
Posts: 17,912

Re: Reading Excel Files by Selecting Certain Sheets

No, you can't read all excel files in the same way unfortunately.

You will need to either batch convert all CSV to XLSX or batch import. Either way, if using SAS a macro is a decent way to go.

I'm assuming all the files have the same structure though. Is the sheet name important to keep as well?

This is a often asked question so there are several solutions on here already that search will turn up.

Contributor
Posts: 55

Re: Reading Excel Files by Selecting Certain Sheets

Thanks, Reeza. I just wrote a small macro that does the job.

Contributor
Posts: 55

Re: Reading Excel Files by Selecting Certain Sheets

would you please provide the batch code...

Super User
Posts: 9,687

Re: Reading Excel Files by Selecting Certain Sheets

can you use libname statement

libname x excel '.........xls';

libname x xlsx '..........xls'

libname x excelcs '..........xls'

then directly copy it frim x library.

data x;

set x.'Data Sheet$'n;

run;

Xia Keshan

Contributor
Posts: 55

Re: Reading Excel Files by Selecting Certain Sheets

thanx keshan :-)

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 495 views
  • 0 likes
  • 4 in conversation