Bind different files from different folders

[NewUsrStat]

Hi guys, 

suppose to have 3 folders named: Country1, Country2, Country3.  In each, there are files named: 

Stats_Q1_C1, Stats_Q1_C2, Stats_Q1_C3, Stats_Q2_C1, Stats_Q2_C2, Stats_Q2_C3. etc until Q27. 

Is there a way to generate an Excel file for each Q* containing the content of C1, C2, C3 together? 

The content are basically frequency tables. 

So, file_Q1 will contain the content of files Stats_Q1_C1, Stats_Q1_C2 and Stats_Q1_C3 in the same sheet (so an Excel file is required) one under the other. 

Thank you in advance

Best  

[yabwon · Accepted solution]

How about that:

1) some fake data:

%let path=/path/with/data;

options dlcreatedir;
libname test "&path.";

libname C1 "&path./Country1";
libname C2 "&path./Country2";
libname C3 "&path./Country3";

%macro createFakeData();
%local i j k;
data 
  %do i = 1 %to 3;
    %do j = 1 %to 27;
      %do k = 1 %to 3; 
        C&i..Stats_Q&j._C&k.
      %end;
    %end;
  %end;
;
set sashelp.class;
run;
%mend createFakeData;

%createFakeData()

2) generate excel:

%macro makeExcel(path=.,j=1);
libname e XLSX "&path./file_Q&j..xlsx";
data e.file_Q&j.;
  set %do i = 1 %to 3;
        C&i..Stats_Q&j._C: /* : is important to grab all */  
      %end;
  ;
run;
%mend makeExcel;

%makeExcel(path=/path/for/excel/,j=1)
%makeExcel(path=/path/for/excel/,j=5)
%makeExcel(path=/path/for/excel/,j=17)

Bart

[Tom]

What kind of files are theses? It is very hard to tell since the names you use do not have extensions on them.

Are you talking about SAS datasets?  If so the filenames should be in all lowercase. Such as 

stats_q1_c3.sas7bat

or are they other types of files?

[Ksharp]

Yes.

As Tom said these files are SAS dataset or CSV/TEXT files ?

We need to know the extension name of these files , so we can code something base on it .

I assumed these files were CSV files and the root folder of Country1,Country2,Country3 was "c:\temp\", 

and you can use OS command too, otherwise you need to resort to the macro %dirtree written by  @Tom  to search all these sub-folders.

https://github.com/sasutils/macros/blob/master/dirtree.sas

%let root_folder= c:\temp ;  *the parent path contains Country1,Country2,Country3 ;
%let want_excel = c:\temp\want.xlsx ;  *the output excel file;




filename x pipe %sysfunc(quote(dir "&root_folder.\*.csv" /s /b )); 
data path;
infile x length=len truncover;
input path $varying200. len;
dsn=cats(scan(path,-2,'\'),'_',scan(path,-2,'.\'));
id=scan(path,-2,'.\');
if lowcase(path) =: "&root_folder.\country";
run;

data _null_;
set path;
call execute(catt('proc import datafile="',path,'" out=',dsn,' dbms=csv replace;guessingrows=max;run;'));
run;

proc sort data=path out=id;
by id;
run;
data _null_;
set id ;
by id;
if first.id then call execute(cat('data ',id,';set '));
call execute(dsn);
if last.id then call execute(catt(";run;proc export data=",id," outfile='&want_excel.' dbms=xlsx replace;sheet='",id,"';run;"));
run;