topic Re: Bind different files from different folders in New SAS User

Bind different files from different folders

NewUsrStat — Thu, 31 Jul 2025 15:03:12 GMT

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

Re: Bind different files from different folders

yabwon — Thu, 31 Jul 2025 15:50:40 GMT

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

Re: Bind different files from different folders

Tom — Thu, 31 Jul 2025 22:10:57 GMT

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?

Re: Bind different files from different folders

Ksharp — Fri, 01 Aug 2025 03:08:18 GMT

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;