read dir all for all xlsx with blank to important all files

ffgsdf — Fri, 26 Aug 2016 12:19:28 GMT

I have wirte a macro to important all xlsx in "C:\SASMACRO\data\F2+Blank\" and read them and merged in a dataset called "a."

However, I have problems is that the file dataset with one variable (filenames), which cannot read the blank.

So, if a xlsx data called "data 01", then it cannot read.

Can some one help me to improve the problems that I can important xlsx file with blank in its name.

Thx.

%macro merge();
proc sql noprint;select count(filenames) INTO :myn from File;
%do i=1 %to &myn;
proc import out=datafile&i datafile="&&file&i" dbms=xlsx /*any data type*/replace;
run;
%end;
data a;
set %do j=1 %to &myn;
datafile&j
%end;
;
run;
%mend merge;
filename lib pipe 'dir "C:\SASMACRO\data\F2+Blank\*.xlsx" /b';
data file;
length filenames $ 40;
infile lib truncover;
input filenames : $;
filenames="C:\SASMACRO\data\F2+Blank\"||filenames;
run;
data _NULL_;
set file;
call symputx('file'||put(_n_,1.),filenames,'G');
run;
options sasautos=work;
%merge()
/*data would be merged in a*/

Re: read dir all for all xlsx with blank to important all files

RW9 — Fri, 26 Aug 2016 12:45:08 GMT

Simplfying your code will help you. Also note it is never a good idea to have any special characters in filenames or paths.

filename lib pipe 'dir "C:\SASMACRO\data\F2+Blank\*.xlsx" /b';

data file;
  infile lib truncover;
  input;
  call execute('proc import datafile="C:\SASMACRO\data\F2+Blank\"'||strip(_infile_)||'" out=inter dbms=xlsx; run;');
  call execute('data want; set want inter; run;');
run;

You will note that each iteration creates inter, then that gets set to want (want should be created before this step!).

topic Re: read dir all for all xlsx with blank to important all files in SAS Programming

read dir all for all xlsx with blank to important all files

Re: read dir all for all xlsx with blank to important all files