topic Re: Convert all XLS files in directory to CSV using SAS in SAS Programming

Convert all XLS files in directory to CSV using SAS

Walternate — Fri, 12 May 2023 16:29:19 GMT

Hi all,

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

Thanks,
Jenna

Re: Convert all XLS files in directory to CSV using SAS

novinosrin — Fri, 12 May 2023 16:50:10 GMT

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)

Re: Convert all XLS files in directory to CSV using SAS

A_Kh — Fri, 12 May 2023 17:55:47 GMT

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.