Reading Excel Files by Selecting Certain Sheets

[finans_sas]

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;

[Reeza]

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?

[finans_sas]

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.

[Reeza · Accepted solution]

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.

[finans_sas]

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

[Rahul_SAS]

would you please provide the batch code...

[Ksharp]

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

[Rahul_SAS]

thanx keshan 🙂