Loop to append files using two macros

[Ciaran OFlynn]

Hi,

I'm appending separate country files in to one (US_1.dta, US_2.dta.........GB_1.dta,GB_2.dta). All of the files are stored in the same folder.

The code I'm using currently to do this is .

use US_1.dta
 
 forvalues  i = 2 / 5 {
    append using US_part`i'
    
}
  
 save US.dta

However, the list of countries has now grown unmaneagable with 1000 files produced in the folder. Can I create a loop that first searches first for a countryname (US, GB, FR etc) " `i' " and then a numbering structure (_1,_2..._5) " `j' " ?

Thanks


Ciaran

[Maarten Buis]

cd ...
local files : dir . files "*.dta"
gettoken firstfile restfiles : files
use `firstfile'
foreach file of local restfiles {
    append using `file'
}

[Ciaran OFlynn]

Hi Maarten,

Thanks for this. Could you talk me through this code a little? Do I need to change any of the variables to my own context? As is, it runs without error but makes no change to the files.

Thanks!

[Maarten Buis]

cd means change directory. This is how you set the working directory. So you don't write cd ... but something like cd h:\mtrack\working

local files : dir . files "*.dta" makes a local macro containing the names of all files in the current directory ( that is what "." means in this context) that end in ".dta"

gettoken firstfile restfiles : files takes the first filename and stores it in the local macro `firstfile' and the remaining filenames and stores those the local macro `restfiles'

use `firstfile' Opens that first file

foreach file of local restfiles {
append using `file'
} loops over the remaining files and appends them to the first file

[Ciaran OFlynn]

Hi Maarten,

Thanks for this. I'm not sure I see here how I get to my solution (apologies if I'm missing something obvious)

In a simple scenario I'm looking to append "file_a_1" to "file_a_2", and then for "file_b_1" to be appended by "file_b_2", and so on.

Does this code do that?



Currently, my code looks like

 use US_part1.dta
 
 forvalues  i = 2 / 32 {
    append using US_part`i'
    
}
  
 save US.dta
 
 
 clear 
 
 use BE_part1.dta
 forvalues  i = 2 / 32 {
    append using BE_part`i'
    
}
  
 save BE_2.dta
 
 forvalues  i = 1 / 32 {
    append using BG_part`i'
    
}
   
 
 save    BG.dta
 
 forvalues  i = 1 / 32 {
    append using BG_part`i'
    
}

However, the country list is now getting too long, and I would like to automate the process for efficiency.

[Maarten Buis]

it simplifies your code by not using that structure at all, but just use one loop for all files. You can nest loops if you like that better. I do that a lot, but like to avoid them when I can. Simple is better.

local countries "BE BG US"
foreach country of local countries {
    use `country'_part1
    forvalues i = 2/32 {
        append using `country'_part`i'
    }
    save `country', replace
}

[Ciaran OFlynn]

Hi Maarten,

This works really well - thank you.

However, one problem I encountered is that I get an error if there are fewer that the stated 'val_2' in the

 
 forvalues i = val_1/val_2

Is there a workaround where this value may change, but the code still runs?

[Maarten Buis]

You can combine the first and second solution:

local countries "BE BG US"
foreach country of local countries {
    local files : dir . files "`country'_part*.dta"
    gettoken firstfile restfiles : files
    use `firstfile'
    foreach file of local restfiles {
        append using `file'
    }
    save `country', replace
}

[Ciaran OFlynn]

This worked perfectly! Thank you for all your help