foreach command

[Muhammad Ramzan]

Hi

I have survey data files for many countries and years. I have to collect data from these files using for each command.

The issue is that the survey year is not common for each country. Country A might be served in 2010, 2012 2015; country B might be surveyed in 2006 and 2018.


how I can proceed please.

Thanks

[Muhammad Ramzan]

the names of the files are countryAyear......................


foreach dataset of numlist 2006 2010 2012 2015 2018 {
foreach name in "countryA" "countryB"{


commands

}
}

will this structure work or not please?

[Jared Greathouse]

Please use dataex to show us what these datasets look like, I don't want a verbal description, I wanna see how the datasets actually look in your machine (no screenshots, just dataex).

Show three data examples, and when I get up and have coffee in 8 hours, I'll see what we've got

[Muhammad Ramzan]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID double(lat longitude Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sum) str4 Year
 0 -179.75  71.25   4.1  17.9    .6   7.1   8.1   6.1  31.1   5.9    5     7  13.1  15.2  121.2 "2016"
 1 -179.75  68.75  15.6  19.6   5.6  10.9     9   7.3  26.8  15.8 35.3  20.9  37.3  31.1  235.2 "2016"
 2 -179.75  68.25  14.5  21.2   4.1   8.4   7.5   6.7  27.3  15.9 41.9  21.9  35.7  42.4  247.5 "2016"
 3 -179.75  67.75  16.1  26.7   3.6   6.7   7.3     8  34.1  18.4   52    26  35.3    59  293.2 "2016"
 4 -179.75  67.25  19.1  33.8   3.3   5.4   7.7   8.7  42.5  20.9 64.4  36.1  37.4  80.8  360.1 "2016"
 5 -179.75  66.75    13  34.3     0    .8   8.6   6.9    49  28.3 81.6  48.9    39 101.4  411.8 "2016"
 6 -179.75  66.25   5.6  32.2     0     0  11.7   8.8  59.1  42.1 94.6  61.7  39.8 108.8  464.4 "2016"
 7 -179.75  65.75   7.3  38.7     0     0  10.9   9.7  57.9  30.7 68.2  61.8  25.5  77.5  388.2 "2016"
 8 -179.75  65.25  11.5  46.9   3.3   1.6  13.1  11.9  59.8  22.4 44.1  63.2  17.7  53.9  349.4 "2016"
 9 -179.75 -16.75 177.5 180.8 189.8 588.8 120.8 124.7 130.4 228.5 87.9 164.1 167.2 523.8 2684.3 "2016"
10 -179.75 -84.75   1.8  11.7  11.6   7.6  12.1     0     0     0    0  16.3  19.7  13.1   93.9 "2016"
11 -179.75 -85.25     0   8.2   7.8   4.8   8.3     0     0     0    0    11  13.8     9   62.9 "2016"
12 -179.75 -85.75     0   5.6     5   2.8   5.4     0     0     0    0   7.3   9.4     6   41.5 "2016"
13 -179.75 -86.25     0     4   3.1   1.6   3.6     0     0     0    0   4.9   6.7   3.9   27.8 "2016"
14 -179.75 -86.75     0   2.7   1.8    .7   2.3     0     0     0    0   3.2   4.7   2.4   17.8 "2016"
15 -179.75 -87.25     0   1.6    .5     0   1.1     0     0     0    0   1.7   2.8     1    8.7 "2016"
16 -179.75 -87.75     0    .4     0     0     0     0     0     0    0    .2     1     0    1.6 "2016"
17 -179.75 -88.25     0     0     0     0     0     0     0     0    0     0    .3     0     .3 "2016"
18 -179.75 -88.75     0     0     0     0     0     0     0     0    0     0    .1     0     .1 "2016"
19 -179.75 -89.25     0     0     0     0     0     0     0     0    0     0    .2     0     .2 "2016"
end

------------------ copy up to and including the previous line ------------------

Listed 20 out of 85794 observations

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID double(lat longitude Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sum) str4 Year
 0 -179.75  71.25   1.6  13.8   3.9  6.5  16.5  10.1  2.9  24.6 14.2 13.1  10.1   5.6  122.9 "2015"
 1 -179.75  68.75  19.8  14.7  13.3  8.5    23  28.5  9.8  54.8 25.5   35  39.8  13.9  286.6 "2015"
 2 -179.75  68.25  21.2  15.3  15.7  7.3  27.3  41.4  3.4  64.4 19.7 34.5  37.2  12.1  299.5 "2015"
 3 -179.75  67.75  25.2  19.3  19.2  8.9  34.2  58.2    4  74.6   17 31.9  33.1  13.2  338.8 "2015"
 4 -179.75  67.25    30  25.3    23 12.2  43.4    78  7.6  83.8 15.5 28.4  28.2  15.4  390.8 "2015"
 5 -179.75  66.75  26.8  27.4  24.2 10.5    57 104.3  9.2  96.7 13.1 25.8  20.6   8.2  423.8 "2015"
 6 -179.75  66.25  21.4  27.6  24.9  8.5  66.9 121.9 17.3 112.6 14.6 27.5  16.9    .4  460.5 "2015"
 7 -179.75  65.75  18.1  24.4  19.8 14.7  49.7  84.2 20.2  93.2 26.5 17.5  11.1     7  386.4 "2015"
 8 -179.75  65.25  18.9    22  18.3 24.1  37.4  52.9 26.5  77.2 39.9 15.6  11.2  17.9  361.9 "2015"
 9 -179.75 -16.75 225.9 342.3 252.3  201 176.9 141.1 76.8 214.8 88.3  108 228.5 141.5 2197.4 "2015"
10 -179.75 -84.75   4.9   9.2  12.2  8.2  10.3     0  1.1     0   .2 14.8  19.8   9.3     90 "2015"
11 -179.75 -85.25   3.2   6.2   8.4  5.4     7     0   .7     0    0  9.7  13.9   5.3   59.8 "2015"
12 -179.75 -85.75     2   4.1   5.7  3.5   4.6     0   .4     0    0  6.2   9.7   2.6   38.8 "2015"
13 -179.75 -86.25   1.3   2.9   3.9  2.2   3.2     0   .2     0    0    4     7    .7   25.4 "2015"
14 -179.75 -86.75    .8   1.9   2.7  1.4   2.2     0   .1     0    0  2.6   5.1     0   16.8 "2015"
15 -179.75 -87.25    .3     1   1.5   .5   1.2     0    0     0    0  1.2   3.2     0    8.9 "2015"
16 -179.75 -87.75     0    .1    .1    0    .1     0    0     0    0    0   1.4     0    1.7 "2015"
17 -179.75 -88.25     0     0     0    0     0     0    0     0    0    0    .7     0     .7 "2015"
18 -179.75 -88.75     0     0     0    0     0     0    0     0    0    0    .6     0     .6 "2015"
19 -179.75 -89.25     0    .3     0    0     0     0    0     0    0    0    .7     0      1 "2015"
end

------------------ copy up to and including the previous line ------------------

Listed 20 out of 85794 observations

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID double(lat longitude Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sum) str4 Year
 0 -179.75  71.25  19.7  10.1 11.9   8.1   4.3   6.6   7.5  5.9 21.4   9.3  11.3   9.2  125.3 "2014"
 1 -179.75  68.75  28.2  23.1 18.8   9.6  30.2  11.3  27.2 22.9 31.5  33.1  37.8  28.9  302.6 "2014"
 2 -179.75  68.25  29.2  28.3 20.2   7.3  46.6   9.1  31.8 25.3 32.2  35.4  38.9  30.2  334.5 "2014"
 3 -179.75  67.75  33.3  37.4 23.1   7.1  60.9  10.6  39.8 30.4 36.5  33.3  40.9  32.2  385.5 "2014"
 4 -179.75  67.25  38.1  46.1 26.7   8.5  73.4  11.2  49.1 36.8 43.3  27.9  44.8  33.7  439.6 "2014"
 5 -179.75  66.75  31.5  48.5 28.3   7.3  87.7   9.7    62 49.7 51.7  22.4  47.6    28  474.4 "2014"
 6 -179.75  66.25  23.3  48.4 29.1   6.6  97.3  11.4  77.6   66 59.5  21.9  48.8  22.1    512 "2014"
 7 -179.75  65.75  31.6  44.7 22.4   5.7  66.4    16  56.7 49.9 49.8  17.1    32  18.9  411.2 "2014"
 8 -179.75  65.25  43.8  42.6 18.9   7.3  38.5  20.8  41.6 37.6 42.2  20.5  21.2  19.9  354.9 "2014"
 9 -179.75 -16.75 218.9 319.1  287 199.9 254.5 158.4 103.4 97.8   61 276.7 240.8 348.1 2565.6 "2014"
10 -179.75 -84.75   5.4   9.4 13.7  10.3  12.6     0     0    0    0    20  19.2  12.7  103.3 "2014"
11 -179.75 -85.25   3.5   6.4  9.4   7.2   8.9     0     0    0    0  14.1  13.2   8.8   71.5 "2014"
12 -179.75 -85.75   2.2   4.1  6.4   4.9     6     0     0    0    0   9.8   8.9   6.1   48.4 "2014"
13 -179.75 -86.25   1.5   2.8  4.3   3.5   4.2     0     0    0    0     7   6.2   4.2   33.7 "2014"
14 -179.75 -86.75   1.1   1.8  2.9   2.4   2.9     0     0    0    0   4.9   4.2   2.9   23.1 "2014"
15 -179.75 -87.25    .5    .9  1.6   1.4   1.6     0     0    0    0     3   2.4   1.7   13.1 "2014"
16 -179.75 -87.75     0     0   .2    .5    .4     0     0    0    0   1.2    .6    .5    3.4 "2014"
17 -179.75 -88.25     0     0    0     0     0     0     0    0    0    .3     0     0     .3 "2014"
18 -179.75 -88.75     0     0    0     0     0     0     0    0    0    .1     0     0     .1 "2014"
19 -179.75 -89.25     0     0    0     0     0     0     0    0    0     0     0     0      0 "2014"
end

------------------ copy up to and including the previous line ------------------

Listed 20 out of 85794 observations

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID double(lat longitude Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sum) str4 Year
 0 -179.75  71.25 14.6  27.8     0   7.4    9  11.4 21.1    29  16.1  21.6  11.6   2.3  171.9 "2012"
 1 -179.75  68.75 24.7  36.2   4.7  21.6 12.5  32.2 38.1  74.3  29.9  20.5     8   7.2  309.9 "2012"
 2 -179.75  68.25 23.2  33.9     5  22.3 11.5  31.9 38.4  75.7  29.6  22.1   9.1  12.9  315.6 "2012"
 3 -179.75  67.75 21.8  32.6   5.8  24.1    9  34.1 38.2    83  28.3  28.2  10.4  24.8  340.3 "2012"
 4 -179.75  67.25 20.2  31.9   6.1  26.8  5.2  37.7 33.9  94.6    23  40.4    13  42.1  374.9 "2012"
 5 -179.75  66.75  9.1  25.4   1.7  26.4  1.7  41.7 27.1 113.3  13.9  59.4  12.5  55.6  387.8 "2012"
 6 -179.75  66.25    0    21     0  26.8  2.2  49.1 30.7 132.7  11.1  69.5  11.6  57.5  412.2 "2012"
 7 -179.75  65.75    0  24.7   3.8  20.4  1.8  37.5 33.8 112.4  15.2  44.4   3.3  45.5  342.8 "2012"
 8 -179.75  65.25   .6  26.9  11.9  15.9  3.4  28.2 39.3  88.3  16.2  26.7    .3  39.4  297.1 "2012"
 9 -179.75 -16.75  583 479.1 375.3 221.1  261 197.5 66.7  84.1 214.8 220.2 237.1 280.7 3220.6 "2012"
10 -179.75 -84.75  4.6   9.7  10.9   8.5 10.6     0    0     0     0  17.3  18.2    11   90.8 "2012"
11 -179.75 -85.25  2.8   6.6   7.2   5.6  7.1     0    0     0     0  11.9  12.3   7.1   60.6 "2012"
12 -179.75 -85.75  1.4   4.3   4.6   3.6  4.5     0    0     0     0   8.1   8.1   4.5   39.1 "2012"
13 -179.75 -86.25   .7     3     3   2.4  3.1     0    0     0     0   5.6   5.6   2.8   26.2 "2012"
14 -179.75 -86.75   .2     2   1.9   1.6    2     0    0     0     0   3.9   3.7   1.7     17 "2012"
15 -179.75 -87.25    0   1.1    .9    .9    1     0    0     0     0   2.2     2    .6    8.7 "2012"
16 -179.75 -87.75    0    .1     0    .1    0     0    0     0     0    .7    .4     0    1.3 "2012"
17 -179.75 -88.25    0     0     0     0    0     0    0     0     0     0     0     0      0 "2012"
18 -179.75 -88.75    0     0     0     0    0     0    0     0     0     0     0     0      0 "2012"
19 -179.75 -89.25    0    .1     0     0    0     0    0     0     0     0     0     0     .1 "2012"
end

------------------ copy up to and including the previous line ------------------

Listed 20 out of 85794 observations

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID double(lat longitude Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Sum) str4 Year
 0 -179.75  71.25 21.1   8.8  26.3   1.2   7.8   5.5     9   6.1   5.3  11.4   6.7   9.6  118.8 "2013"
 1 -179.75  68.75 30.4  10.7    24   6.5    18  16.3  22.8  33.1  24.9  46.7  23.9  21.8  279.1 "2013"
 2 -179.75  68.25 30.2  11.9  24.1   7.2  17.5  15.7  31.8  29.3  21.7  48.5    22  22.3  282.2 "2013"
 3 -179.75  67.75 30.3  13.6  25.2   8.6  15.8  18.2    52  27.2  20.1  49.6    23  26.1  309.7 "2013"
 4 -179.75  67.25 30.2  14.2  27.4  10.1    11  21.1  81.7  22.9  17.9  50.8  27.3  32.7  347.3 "2013"
 5 -179.75  66.75 20.3   8.3  26.1   7.1     5  22.4 122.4  18.7  14.5  53.7  27.7  35.2  361.4 "2013"
 6 -179.75  66.25 11.1   4.9  24.2   4.2   4.9  25.1 152.1  22.9  15.8  57.3  25.5  33.1  381.1 "2013"
 7 -179.75  65.75 20.4   6.3  30.7  10.7   5.1  25.9 112.7  35.5  16.7  44.3  26.3  21.8  356.4 "2013"
 8 -179.75  65.25 34.3   8.6  40.3    19   7.8  26.9  79.5  44.5    17    39    34  15.2  366.1 "2013"
 9 -179.75 -16.75  261 496.7 482.2 222.7 196.8 211.6 141.2 175.3 190.9 426.8 258.1 321.4 3384.7 "2013"
10 -179.75 -84.75  4.5   9.6    12   8.4  12.4     0     0     0     0  17.2  18.7  11.1   93.9 "2013"
11 -179.75 -85.25  2.6   6.5   8.1   5.6   8.6     0     0     0     0  11.8  12.7   7.4   63.3 "2013"
12 -179.75 -85.75  1.3   4.2   5.3   3.5   5.7     0     0     0     0     8   8.1   4.8   40.9 "2013"
13 -179.75 -86.25   .6   2.9   3.5   2.3     4     0     0     0     0   5.6   5.5   3.1   27.5 "2013"
14 -179.75 -86.75   .2   1.8   2.3   1.4   2.7     0     0     0     0   3.9   3.6     2   17.9 "2013"
15 -179.75 -87.25    0     1   1.2    .7   1.5     0     0     0     0   2.3     2    .9    9.6 "2013"
16 -179.75 -87.75    0     0     0     0    .3     0     0     0     0    .7    .4     0    1.4 "2013"
17 -179.75 -88.25    0     0     0     0     0     0     0     0     0     0     0     0      0 "2013"
18 -179.75 -88.75    0     0     0     0     0     0     0     0     0     0     0     0      0 "2013"
19 -179.75 -89.25    0     0     0     0     0     0     0     0     0     0     0     0      0 "2013"
end

------------------ copy up to and including the previous line ------------------

Listed 20 out of 85794 observations

[Nick Cox]

If you append these files, year will be included as a variable. Otherwise country is not a variable in what you have shown us. Perhaps it is included in or implied by the filename.

[Hemanshu Kumar]

I am going to assume that the data files are all in your current working directory, and that these are the only Stata datasets located in this directory, whose file names end with the year.

Given these assumptions, you could try something like the following (warning: untested code):

local years 2010 2012 2015 2016 // include all possible years here

foreach year of local years {
    local files_`year': dir . files "*`year'.dta"
    foreach file of local files_`year' {
        ... your commands here ...
    }
}

where within the inner loop, you should be able to access the specific file using `"`file'"'

[Muhammad Ramzan]

I am going to assume that the data files are all in your current working directory, and that these are the only Stata datasets located in this directory, whose file names end with the year.

Given these assumptions, you could try something like the following (warning: untested code):

local years 2010 2012 2015 2016 // include all possible years here

foreach year of local years {
local files_`year': dir . files "*`year'.dta"
foreach file of local files_`year' {
... your commands here ...
}
}

where within the inner loop, you should be able to access the specific file using `"`file'"'

Thanks

But the name of the files is countryA2010 countryA2012 countryB2006 countryB2010 countryB2018..

[Hemanshu Kumar]

That should not be a problem. The pattern we are looking for only requires that all file names end with the year.

Do your command within the loop require the country name? If not, my code should be adequate for you. If you do need the country name -- again, not to access the file itself, but for some of the commands inside the loop -- then the code will need some modification.

[Muhammad Ramzan]

That should not be a problem. The pattern we are looking for only requires that all file names end with the year.

Do your command within the loop require the country name? If not, my code should be adequate for you. If you do need the country name -- again, not to access the file itself, but for some of the commands inside the loop -- then the code will need some modification.

Yes, the command within the loop requires the country names as well.

[Hemanshu Kumar]

You can access the country name in the inner loop with the following local:

local country = substr("`file'",1,length("`file'")-8)

which assumes that the filename is always something like countrynameYYYY.dta

[Muhammad Ramzan]

Thanks

I am getting the following error message

[Hemanshu Kumar]

As I stated in #6,

where within the inner loop, you should be able to access the specific file using `"`file'"'

So you need to do

use `"`file'"', clear

instead of your current use command.

[Muhammad Ramzan]

Hemanshu Kumar


I am in a similar situation. I have Stata data files on a number of countries, and each country has seven files.


I have to get a few variables from each file, merge them using ID and append them with the data from other countries. What could be the efficient way to do it, please?


Like I have to get few variables from file 1 , few variables from file 2 .... for the same country , merge them , do the same for country 2 and at the end I have to append the data for all countries.

Thanks

[Hemanshu Kumar]

You should be able to make it work using something like this:

local countries `" "Bangladesh" "Ghana" "'

local filetypes F2 F3 F4 F5 F6 F7

local keepvars_ID var1 var2
local keepvars_F2 var3
local keepvars_F3 var4 var5 var6
local keepvars_F4 var7 var8
local keepvars_F5 var9
local keepvars_F6 var10 var11 var12
local keepvars_F7 var13 var14

local idvar my_id_var

clear
save "all_data", replace emptyok

foreach ctry of local countries {
    
    use "`ctry'/`ctry'-ID", clear
    keep `idvar' `keepvars_ID'
    foreach typ of local filetypes {
        merge 1:1 `idvar' using "`ctry'/WHS-`ctry'_`typ'", keepusing(`keepvars_`typ'') gen(_merge_`typ')
    }
    save "`ctry'_data", replace
    append using "all_data"
    save "all_data", replace
}

where you should replace the italicised variable names with the actual variables you need, you should see whether you a need a 1:1 merge or some other type, and where I am assuming that the folder for each country's data is just named with the country name; you will need to modify the use command if it is different.

Also the save command above (which creates a separate file for each country) is unnecessary, but might help troubleshoot problems. You can get rid of it if you like.

[Muhammad Ramzan]

You should be able to make it work using something like this:

local countries `" "Bangladesh" "Ghana" "'

local filetypes F2 F3 F4 F5 F6 F7

local keepvars_ID var1 var2
local keepvars_F2 var3
local keepvars_F3 var4 var5 var6
local keepvars_F4 var7 var8
local keepvars_F5 var9
local keepvars_F6 var10 var11 var12
local keepvars_F7 var13 var14

local idvar my_id_var

clear
save "all_data", replace emptyok

foreach ctry of local countries {

use "`ctry'/`ctry'-ID", clear
keep `idvar' `keepvars_ID'
foreach typ of local filetypes {
merge 1:1 `idvar' using "`ctry'/WHS-`ctry'_`typ'", keepusing(`keepvars_`typ'') gen(_merge_`typ')
}
save "`ctry'_data", replace
append using "all_data"
save "all_data", replace
}

where you should replace the italicised variable names with the actual variables you need, you should see whether you a need a 1:1 merge or some other type, and where I am assuming that the folder for each country's data is just named with the country name; you will need to modify the use command if it is different.

Also the save command above (which creates a separate file for each country) is unnecessary, but might help troubleshoot problems. You can get rid of it if you like.

Thanks