Combine loops into a single double loop

[Federica Di Marcantonio]

Dear All,

I would like to create a loop that allows me to:
1) sum-up the rows values of a set of variables q18_*_2 (hectares) by a predefined groups of values in q18_*_1 (i.e. cereals groups all the values coded

D01

D02

D03

D04

D05

D06

D07

D08

D09

D09A

D09B

D10

D11

D12

2) and replace those values in a new set of variables that represent the different groups of the values contained in q18_*_1

So using the data sample I provide below, ideally I would contract something like ( i know that the expression is wrong):

foreach i of varlist  q18_*_2 {
 foreach var of varlist  q18_*_1 {
replace cereals=rowtotal(`i') if `var'=="D01"|`var'=="D02"|`var'=="D03"|`var'=="D04"|`var'=="D05"|`var'=="D06"|`var'=="D07"|`var'=="D08" 
replace pulses=rowtotal(`i') if `var'=="D09"|`var'=="D09A"|`var'=="D09B"
replace roots_tubers=rowtotal(`i') if `var'=="D10"|`var'=="D11"|`var'=="D12"
}

* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 q18_1_1 double q18_1_2 str4 q18_2_1 float q18_2_2 str4 q18_3_1 float q18_3_2
"D14A"   6 "J07"   20 "J04"  10
"J08"  220 "D06"   40 "D18A" 90
"G04A"   2 ""       . ""      .
"J02"   18 "J07"  120 "J04"  32
"D01"   30 "D06"   90 ""      .
"D02"    4 "D08"    8 "D01A"  1
"D18A"  35 "D01"    3 "D04"   2
"D01"    8 "D18A"  14 "D04"   2
"D08"    2 "G01A"   8 "D18A"  3
"D18A"  20 "D04"    5 "J03"  14
"D01"    4 "D04"    5 "D06"   6
"D18B" 160 "D06"   20 "D04"  10
"D18B"  25 "D06"   12 "D04"   8
"J04"    4 "J08"   40 "D18A" 10
"D18A"  10 "D01"   20 "D06"  15
"D26"   20 "D01"   20 "D06"  25
"D11"   10 "D26"   20 "D04"  25
"D26"   25 "D01"   25 "D04"  25
"D11"   10 "D18A"  25 "D01"  25
"D11"   10 "D18A"  15 "D01"  15
"D26"   20 "D18A"  20 "D06"  20
"D26"   20 "D01"   20 "D06"  20
"D01"   20 "D06"   20 "D28"  20
"D18A"  15 "D01"   15 "D04"  15
"D26"   25 "D18A"  25 "D01"  25
"D11"   10 "D01"   20 "D27"  10
"D26"   15 "D18A"  15 "D06"  15
"D11"   10 "D01"   15 "D27"  10
"D26"   20 "D18A"  20 "D01"  20
"D20"   20 "D08"   10 "D11"  10
"D18A"  20 "D02"   20 "D06"  15
"D18A"  25 "D01"   35 "D06"  13
"D06"   13 "D14A"   4 "D07"   5
"D18A"  35 "D01"   27 "D06"  18
"D18A"  40 "D06"    8 "D07"  16
"D18A"  36 "D01"   33 "D06"  25
"D18A"  25 "D01"   15 "D06"  15
"D18A"  16 "D01"   27 "D06"  18
"D18A"  32 "D01"   15 "D06"  28
"D18A"  23 "D01"    8 "D06"  14
"G01A"  12 ""       . ""      .
"G01A"   8 "G04A"   5 ""      .
"D18A"  27 "D06"   16 "D07"  12
"G01A"   6 "D14A"  12 ""      .
"D11"   10 "D26"   10 "D18A" 15
"D18A"  10 "D04"   20 "D28"  20
"D01"   15 "G01A"   2 "D06"   7
"G04A"  22 ""       . ""      .
"D18A"  13 "D01"   14 "D06"   2
"G01A"   8 "G04A"   7 ""      .
"D18A"  18 "D01"    9 "D06"   8
"D18A"  22 "D06"   18 "D07"   8
"D18A"   9 "D01"   16 "D06"   7
"G04A"  18 ""       . ""      .
"G01A"   6 "G04A"   5 ""      .
"G04A"  17 ""       . ""      .
"D18A"   2 "G01A"  12 "G04A"  8
"G01A"  13 "G04A"  16 ""      .
"G04A"  11 ""       . ""      .
"G01A"  15 "G04A"  13 ""      .
"G01A"   8 "D06"    6 ""      .
"G04A"  16 ""       . ""      .
"G04A"  27 ""       . ""      .
"D18A"  11 "D01"   18 "D06"   8
"D18A"  15 "D01"   12 "D06"  13
"D18A"  21 "D01"    8 "D06"  13
"D18A"  10 "D01"    8 "D06"   8
"G01A"  23 ""       . ""      .
"G01A"  11 ""       . ""      .
"G04A"  15 ""       . ""      .
"G04A"  11 ""       . ""      .
"G01A"   8 "G04A"   6 ""      .
"D18A"   3 "D01"    6 "D06"   8
"G01A"  15 "G04A"   8 ""      .
"G01A"   7 ""       . ""      .
"G04A"  14 ""       . ""      .
"D18A"  15 "D01"    7 "D06"  11
"G04A"  18 ""       . ""      .
"D18A"   7 "D01"    9 "D06"   8
"G01A"  12 "D06"    7 ""      .
"D18A"  15 "D06"    7 "D28"   4
"D18A"  11 "D01"    7 "D06"   8
"D18A"   8 "D01"   10 "D06"  12
"D14A"   5 "D15"    2 ""      .
"D15"    2 "D07"    8 ""      .
"G04A"  18 ""       . ""      .
"G01A"  12 ""       . ""      .
"G01A"  16 ""       . ""      .
"D27"    3 "D14A"   2 "D15"   2
"G04A"   3 ""       . ""      .
"D18A"   8 "D06"   15 "D28"  15
"G04A"  18 ""       . ""      .
"G01A"  14 ""       . ""      .
"D18B"  35 "D18D"  15 "D01"  30
"D18B"  25 "D18A"  10 "D06"  15
"D18B"  20 "D11"   10 "D26"  20
"D18B"  25 "D11"   10 "D06"  20
"D18B"  15 "D11"   10 "D26"  15
"D18B"  25 "D18A"  25 "D28"  25
"D18B"  20 "D11"   10 "D26"  15
end

Do you have any suggestions?
Thanks

Federica

[Clyde Schechter]

I'm not at all sure I understand what you are trying to do here. I doubt that loops are actually involved in the solution. Maybe it's something like this:

gen `c(obs_t)' obs_no = _n
frame put _all, into(working)
frame working {
    reshape long q18_@_1 q18_@_2, i(obs_no) j(_j)
    rename q18__1 subcategory
    rename q18__2 hectares
    gen category = "cereal" if inrange(subcategory, "D01", "D08")
    replace category = "pulse" if inlist(subcategory, "D09A", "D09B")
    replace category = "root_tuber" if inrange(subcategory, "D10", "D12")
    replace category = "other" if missing(category)

    collapse (sum) total_hectares_ = hectares, by(obs_no category)
    reshape wide total_hectares_, i(obs_no) j(category) string
}
frlink 1:1 obs_no, frame(working)
frget total_hectares_*, from(working)

Now, the variables q18_*_2 contain many codes that do not fall into the list you show in #1. In the above code, I've just put those into a single "other" category. But you may want to further ramify the variable category that I created to account for those other values. Also, on the one hand you say in words that cereals encompass everything from D01 through D12, although when you tried coding this problem yourself, you only have D01 through D08 as cereals. Not sure what to make of that. I went with D01 through D10.

Anyway, if this isn't what you want, or at least close enough that you can adapt it, when posting back please work up by hand, and show, a small example that shows the results you actually want to get and I'll try to revise accordingly. At least for now, I'm not at all clear what you're trying to get.

[Federica Di Marcantonio]

Hi Clyde,

thanks a lot!!
This is exactly what I needed. The database I provided did not include all the information (neither now, it exceeds linesize limit), but i post a new one with the command i use.

gen `c(obs_t)' obs_no = _n
frame put _all, into(working)
frame working {
    reshape long q18_@_1 q18_@_2, i(obs_no) j(_j)
    rename q18__1 subcategory
    rename q18__2 hectares
    gen category = "cereal" if inrange(subcategory, "D01", "D08")
    replace category = "pulse" if inlist(subcategory,"D09", "D09A", "D09B", "D09C",  "D09D")
    replace category = "root_tuber" if inlist(subcategory, "D10", "D11", "D12")
    replace category="tabac_fibrec" if inrange(subcategory, "D23", "D35")
    replace category="veg_flower" if inlist(subcategory, "D14", "D14A", "D14B", "D15", "D16", "D17")
    replace category="forage_seeds"  if inlist(subcategory,"D18", "D18A", "D18B", "D18C", "D18D", "D18E", "D19", "D20" )
    replace category="grassland" if inlist(subcategory, "F01", "F02")
    replace category="fruit" if inlist(subcategory,"G01", "G01A", "G01B", "G01C", "G01D", "G02")
    replace category="olive" if inlist(subcategory,"G03", "G03A", "G03B")
    replace category="vineyard" if inlist(subcategory, "G04", "G04A", "G04B", "G04C", "G04D")
    replace category="other_pcrops" if inlist(subcategory,"G05", "G06", "G07" "I02")
    replace category="equidae" if inlist(subcategory,"J01")
    replace category="bovine" if inlist(subcategory,"J02", "J03", "J04", "J05", "J06", "J07", "J08")
    replace category="dairy" if inlist(subcategory,"J07")
    replace category="sheep_goat" if inlist(subcategory,"J09", "J09A", "J09B", "J10", "J10A", "J10B")
    replace category="pigs" if inlist(subcategory,"J11", "J12", "J13")
    replace category="poultry" if inlist(subcategory,"J14", "J15", "J16", "J16A", "J16B", "J16B", "J16C", "J16D")
    replace category="rabbit" if inlist(subcategory,"J17")
    replace category = "other" if missing(category) // "D21" and D22 Fallow land without any subsidies or set-aside incentive schemes

    collapse (sum) total_hectares_ = hectares, by(obs_no category)
    reshape wide total_hectares_, i(obs_no) j(category) string
}

frlink 1:1 obs_no, frame(working)
frget total_hectares_*, from(working)

input str4 q18_1_1 double q18_1_2 str4 q18_2_1 float q18_2_2 str4 q18_3_1 float q18_3_2 str4 q18_4_1 float q18_4_2 str4 q18_5_1 float q18_5_2 str4 q18_6_1 float q18_6_2 str4 q18_7_1 float q18_7_2 str4 q18_8_1 float q18_8_2
"J18" 45 "D18A" 12 "G01A" 12 "D14A" 2 "D15" 1 "J15" 120 "F01" 11 "" .
"D18D" 14 "D01" 12 "G01A" 6 "D06" 18 "G04B" 14 "" . "" . "" .
"D18B" 16 "G01A" 4 "D14A" .5 "G04B" 1.5 "" . "" . "" . "" .
"D18B" 21 "D01" 8 "G01A" 8 "F01" 14 "G04A" 12 "" . "" . "" .
"G06" 10 "D18B" 22 "D18C" 6 "J04" 10 "J08" 12 "D06" 4 "J07" 48 "G04B" 2
"D18A" 28 "D14A" 4 "D15" 2 "" . "" . "" . "" . "" .
"D18B" 5 "D18D" 5 "D18C" 8 "D18A" 10 "F01" 20 "" . "" . "" .
"D18B" 22 "D18D" 16 "D18C" 6 "J04" 24 "J02" 12 "J08" 46 "J05" 5 "J06" 22
"D18B" 25 "J04" 12 "J03" 4 "J02" 16 "D18A" 32 "J06" 8 "D06" 12 "D04" 10
"D18B" 15 "D18D" 20 "D18C" 18 "D18A" 10 "D06" 10 "G04B" 1 "" . "" .
"D18B" 12 "D18D" 32 "D18C" 22 "J09B" 10 "J09A" 22 "F01" 31 "G04B" 4 "" .
"D18B" 32 "J04" 22 "J02" 14 "J08" 22 "D18A" 42 "D01" 12 "D27" 4 "J07" 92
"G06" 12 "G01A" 4 "D14A" 4 "D15" 2 "J15" 120 "G04B" 2 "" . "" .
"D18A" 3 "D01" 6 "D06" 8 "D28" 5 "" . "" . "" . "" .
"D18A" 4 "D07" 5 "G04A" 6 "" . "" . "" . "" . "" .
"D18A" 13 "D01" 5 "G01A" 3 "D27" 4 "D06" 8 "D28" 4 "" . "" .
"D18A" 13 "D01" 13 "D06" 8 "G04A" 7 "" . "" . "" . "" .
"D18A" 16 "D01" 27 "D06" 18 "" . "" . "" . "" . "" .
"D18A" 20 "D01" 13 "D06" 11 "G04A" 3 "" . "" . "" . "" .
"D18A" 22 "D06" 9 "D28" 4 "" . "" . "" . "" . "" .
"D18A" 23 "D01" 8 "D06" 14 "D28" 13 "" . "" . "" . "" .
"D18A" 25 "D01" 15 "D06" 15 "D14A" 23 "D07" 8 "" . "" . "" .
"D18A" 25 "D01" 35 "D06" 13 "" . "" . "" . "" . "" .
"D18A" 32 "D01" 15 "D06" 28 "D28" 15 "" . "" . "" . "" .
"D18A" 35 "D01" 27 "D06" 18 "" . "" . "" . "" . "" .
"D18A" 36 "D01" 33 "D06" 25 "D07" 4 "" . "" . "" . "" .
"D18A" 40 "D06" 8 "D07" 16 "" . "" . "" . "" . "" .
"D20" 14 "D18B" 22 "D18D" 17 "D08" 8 "D01" 21 "D06" 32 "D14A" 40 "D12" 11
"G04A" 11 "" . "" . "" . "" . "" . "" . "" .
"G01A" 6 "G04A" 12 "" . "" . "" . "" . "" . "" .
"G04A" 18 "" . "" . "" . "" . "" . "" . "" .
"G04A" 15 "" . "" . "" . "" . "" . "" . "" .
"G04B" 8 "" . "" . "" . "" . "" . "" . "" .
"G01A" 10 "D14A" 3 "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G01A" 8 "G04A" 8 "" . "" . "" . "" . "" . "" .
"G04A" 18 "" . "" . "" . "" . "" . "" . "" .
"G01A" 7 "G03B" 3 "D14A" 5 "D15" 3 "" . "" . "" . "" .
"G01A" 23 "" . "" . "" . "" . "" . "" . "" .
"G01A" 9 "G04A" 15 "" . "" . "" . "" . "" . "" .
"D06" 3 "G04A" 11 "" . "" . "" . "" . "" . "" .
"G01A" 10 "G04A" 8 "" . "" . "" . "" . "" . "" .
"G04A" 8 "" . "" . "" . "" . "" . "" . "" .
"G01A" 5 "G04A" 11 "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 11 "" . "" . "" . "" . "" . "" .
"G01A" 7 "G04A" 12 "" . "" . "" . "" . "" . "" .
"G01A" 9 "G04A" 8 "" . "" . "" . "" . "" . "" .
"G01A" 6 "G04A" 11 "" . "" . "" . "" . "" . "" .
"G04A" 22 "" . "" . "" . "" . "" . "" . "" .
"G04A" 15 "" . "" . "" . "" . "" . "" . "" .
"G01A" 16 "" . "" . "" . "" . "" . "" . "" .
"G01A" 11 "" . "" . "" . "" . "" . "" . "" .
"D14A" 5 "D15" 2 "" . "" . "" . "" . "" . "" .
"D01" 27 "D27" 6 "D06" 13 "D28" 7 "" . "" . "" . "" .
"G04A" 22 "" . "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 6 "" . "" . "" . "" . "" . "" .
"D01" 5 "G01A" 7 "G04A" 15 "" . "" . "" . "" . "" .
"G01A" 8 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G01A" 8 "D14A" 3 "D15" 1 "" . "" . "" . "" . "" .
"G04A" 7 "" . "" . "" . "" . "" . "" . "" .
"G04A" 7 "" . "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 9 "" . "" . "" . "" . "" . "" .
"G01A" 13 "" . "" . "" . "" . "" . "" . "" .
"G04A" 11 "" . "" . "" . "" . "" . "" . "" .
"G01A" 7 "D14A" 5 "D15" 3 "" . "" . "" . "" . "" .
"G01A" 12 "" . "" . "" . "" . "" . "" . "" .
"G01A" 12 "" . "" . "" . "" . "" . "" . "" .
"G01A" 6 "G04B" 4 "" . "" . "" . "" . "" . "" .
"G01A" 12 "" . "" . "" . "" . "" . "" . "" .
"G01A" 16 "" . "" . "" . "" . "" . "" . "" .
"D06" 13 "D14A" 4 "D07" 5 "" . "" . "" . "" . "" .
"D15" 2 "D07" 8 "" . "" . "" . "" . "" . "" .
"G01A" 3 "D06" 3 "G04A" 10 "" . "" . "" . "" . "" .
"G01A" 9 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G04A" 12 "" . "" . "" . "" . "" . "" . "" .
"G04A" 18 "" . "" . "" . "" . "" . "" . "" .
"D01" 9 "G04A" 13 "" . "" . "" . "" . "" . "" .
"D01" 8 "G04A" 12 "" . "" . "" . "" . "" . "" .
"G04A" 15 "" . "" . "" . "" . "" . "" . "" .
"G04A" 12 "" . "" . "" . "" . "" . "" . "" .
"G01A" 12 "D14A" 2 "" . "" . "" . "" . "" . "" .
"D06" 3 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G04A" 15 "" . "" . "" . "" . "" . "" . "" .
"F01" 4 "G04A" 13 "" . "" . "" . "" . "" . "" .
"G04A" 13 "" . "" . "" . "" . "" . "" . "" .
"G01A" 12 "" . "" . "" . "" . "" . "" . "" .
"G01A" 8 "D06" 6 "G04A" 4 "" . "" . "" . "" . "" .
"G04A" 18 "" . "" . "" . "" . "" . "" . "" .
"G01A" 6 "G04B" 6 "" . "" . "" . "" . "" . "" .
"G01A" 3 "G04A" 6 "" . "" . "" . "" . "" . "" .
"G04A" 6 "" . "" . "" . "" . "" . "" . "" .
"D18B" 4 "J02" 6 "J08" 8 "F01" 32 "" . "" . "" . "" .
"D18D" 35 "D01" 12 "G04B" .5 "" . "" . "" . "" . "" .
"D18D" 16 "D18C" 8 "D30" 3 "J16D" 120 "D18A" 22 "D28" 12 "G04B" 2 "" .
"D18B" 18 "D18C" 6 "J04" 12 "J02" 14 "J08" 21 "D18A" 35 "G04B" 6 "" .
"D18B" 40 "D18D" 25 "G01A" 8 "F01" 40 "G04A" 14 "" . "" . "" .
"D18B" 30 "D18D" 20 "J05" 30 "D18A" 25 "F01" 40 "G04A" 22 "" . "" .
"D18D" 82 "D01" 22 "J09B" 250 "G04A" 22 "" . "" . "" . "" .
end

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