st: Routine for merging many text-files

[Simon Falck <simon.falck@abe.kth.se>]

Hi,

I have about 100 text files that I want to merge into a longitudinal database and wonder if there is a routine that I can apply to ease this work.

The text-files have different periods but the same datastructure, which is a wide format that looks something like this,

Id  y1  y2  y3
1  10  30  40
2  11  31  41
...and so on..

If I would apply the standard procedure to create the database the script would look something like this for two text-files, (as an example but would look the same for the 100 text-files)

*Insert dataset 1 from textfile and save as .dta
insheet using "C:\User\dataset1", tab reshape long y, i(id) j(year) rename y var1 save "C:\User\dataset1.dta"

*Insert dataset 2 from textfile and save as .dta
insheet using "C:\User\dataset2", tab reshape long y, i(id) j(year) rename y var2 save "C:\User\dataset2.dta"

*Merge dataset 1 and 2 to key-file containing joint id´s
use "C:\User\id.dta"
merge 1:m id year using "C:\User\dataset1.dta"
drop _merge
merge 1:m id year using "C:\User\dataset2.dta"
drop _merge

The resulting database would look something like this,

Id  year  var1 var2
1     1         10   100
1     2         15    75
1     3        20    65
2     1       11    112
2     2       17     80
3     1       36    110
...and so on..

Since I have about 100 text-files that (1) needs to be converted into .dta, (2) reshaped into a long-format, (3) rename variables, and (4) merged into  a joint database, I wonder if someone know how I could write a routine to ease this work which otherwise is repetitive and result in a very large .do file.

Thanks in advance
Simon


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/