DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

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*/


Accepted Solutions
Solution
‎08-26-2016 09:13 AM
Super User
Super User
Posts: 7,392

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

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!).  

View solution in original post


All Replies
Solution
‎08-26-2016 09:13 AM
Super User
Super User
Posts: 7,392

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

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!).  

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 162 views
  • 0 likes
  • 2 in conversation