topic Re: Reading Excel Files by Selecting Certain Sheets in SAS Procedures

Reading Excel Files by Selecting Certain Sheets

finans_sas — Thu, 08 Jan 2015 21:41:15 GMT

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;

Re: Reading Excel Files by Selecting Certain Sheets

Reeza — Thu, 08 Jan 2015 21:57:19 GMT

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?

Re: Reading Excel Files by Selecting Certain Sheets

finans_sas — Thu, 08 Jan 2015 22:04:48 GMT

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.

Re: Reading Excel Files by Selecting Certain Sheets

Reeza — Thu, 08 Jan 2015 22:17:16 GMT

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.

Re: Reading Excel Files by Selecting Certain Sheets

finans_sas — Thu, 08 Jan 2015 23:38:33 GMT

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

Re: Reading Excel Files by Selecting Certain Sheets

Rahul_SAS — Thu, 15 Jan 2015 12:32:40 GMT

would you please provide the batch code...

Re: Reading Excel Files by Selecting Certain Sheets

Ksharp — Thu, 15 Jan 2015 12:52:39 GMT

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

Re: Reading Excel Files by Selecting Certain Sheets

Rahul_SAS — Fri, 16 Jan 2015 05:16:42 GMT

thanx keshan 🙂