Hi all,
Just looking for a quick and dirty macro which can convert all XLS files in a given directory to CSV.
Thanks,
Jenna
I think it's better done using a Microsoft based tool for Batch converting all excel files to CSV in a folder or library rather than SAS as a primary tool for this purpose. Please see if the following link helps -
How to batch convert multiple Excel files to CSV files in Excel? (extendoffice.com)
I think it's better done using a Microsoft based tool for Batch converting all excel files to CSV in a folder or library rather than SAS as a primary tool for this purpose. Please see if the following link helps -
How to batch convert multiple Excel files to CSV files in Excel? (extendoffice.com)
One way to do this is you can try filename statement with windows command to read all xls files in the directory, then save file name and path in sas dataset, to import and export them in the following data step using call execute routine.
The example code would be:
filename xlslist pipe 'dir /b /s "C:\myfolder\*.xls" ';
data temp;
infile xlslist truncover;
input filepath $200.;
filename= scan(filepath, -1, '\');
proc print;run;
data _null_;
set temp end=eof;
call execute("proc import datafile="||quote(filepath)||' '||"out="||scan(filename,1, '.')||' '||"dbms="||scan(filename, 2, '.')||"replace; run;");
if eof then do;
call execute("proc export outfile="||quote(filepath)||' '||"data=work."||scan(filename,1, '.')||' '||"dbms=csv replace; run;");
end;
run;
PS: I used proc import, and call execute routine is something I rarely use, so there could be more efficient ways to write this code.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.