looping multiple files to have one summary table

[Meshal AlQhtani]

Hi everyone,

I am trying to have one output summary table in one Excel file from multiple files that have sam variable names "epiweek" and "region", the files have different observations count.

here an example of my data file
clear
input byte epiweek str6 region
1 "abha "
1 "jeddah"
1 "jouf"
1 "hasaa"
1 "jazan"
1 "najran"
1 "makkah"
1 "makkah"
1 "jeddah"
1 "hasaa"

I tried using the below commands but it gives me separate summary output files xlsx. which is not the goal, they should be in one file.
_________________________
clear all
forvalues d = 1/3 {
use "/Users/Desktop/multi_files/example_`d'.dta", clear
encode region, gen(regs)
dtable i.regs, export("/Users/Desktop/multi_files/example4", as(xlsx) sheet(Sheet1) cell(A1) replace)
}

kindly for your feedback please

Meshal

[Clyde Schechter]

Well, since you put the -dtable- inside the loop that reads the files, of course, Stata carries out -dtable- on each file separately. So the -dtable- command should come out of the loop.

You don't really make clear how you want to combine the data in the three files. At a guess, probably what you need to do is append them all together. You can do that in a loop, but you don't even need to.

clear
local come_back `c(pwd)'
cd "/Users/Desktop/multi_files
append using example_1 example_2 example_3
cd "`come_back'"
encode region, gen(regs)
dtable i.regs, export("/Users/Desktop/multi_files/example4", as(xlsx) sheet(Sheet1) cell(A1) replace)

[Meshal AlQhtani]

Thank you Mr. Clyde, it was very helpful!!