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;
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.
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?
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.
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.
Thanks, Reeza. I just wrote a small macro that does the job.
would you please provide the batch code...
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
thanx keshan 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.