Convert all XLS files in directory to CSV using SAS

[Walternate]

Hi all,

Just looking for a quick and dirty macro which can convert all XLS files in a given directory to CSV.

Thanks,
Jenna

[novinosrin · Accepted solution]

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)

[A_Kh]

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.