How to generate a variable list and tag the groups they appear in.

[May Blake]

I have a normal list of variables like below. There is a group variable with three groups 1, 2, and 3. What I would like to do is generate a list and have a 3 columns tag the groups that appear for each variable (Group1, Group2 and Group3 and each one will just be flagged as Y/N). For example, if headroom has data for foreign but not domestic, the new column foreign will be Yes and the one for domestic is No. If both foreign and domestic have data on trunk, then the columns for Domestic and foreign will both have Yes.


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

* Example generated by -dataex-. For more info, type help dataex
clear
input str18 make int(price mpg rep78) float headroom int(trunk weight length turn displacement) float gear_ratio byte foreign
"AMC Concord"        4099 22 3 2.5 11 2930 186 40 121 3.58 0
"AMC Pacer"          4749 17 3   3 11 3350 173 40 258 2.53 0
"AMC Spirit"         3799 22 .   3 12 2640 168 35 121 3.08 0
"Buick Century"      4816 20 3 4.5 16 3250 196 40 196 2.93 0
"Buick Electra"      7827 15 4   4 20 4080 222 43 350 2.41 0
"Buick LeSabre"      5788 18 3   4 21 3670 218 43 231 2.73 0
"Buick Opel"         4453 26 .   3 10 2230 170 34 304 2.87 0
"Buick Regal"        5189 20 3   2 16 3280 200 42 196 2.93 0
"Buick Riviera"     10372 16 3 3.5 17 3880 207 43 231 2.93 0
"Buick Skylark"      4082 19 3 3.5 13 3400 200 42 231 3.08 0
"Cad. Deville"      11385 14 3   4 20 4330 221 44 425 2.28 0
"Cad. Eldorado"     14500 14 2 3.5 16 3900 204 43 350 2.19 0
"Cad. Seville"      15906 21 3   3 13 4290 204 45 350 2.24 0
"Chev. Chevette"     3299 29 3 2.5  9 2110 163 34 231 2.93 0
"Chev. Impala"       5705 16 4   4 20 3690 212 43 250 2.56 0
"Chev. Malibu"       4504 22 3 3.5 17 3180 193 31 200 2.73 0
"Chev. Monte Carlo"  5104 22 2   2 16 3220 200 41 200 2.73 0
"Chev. Monza"        3667 24 2   2  7 2750 179 40 151 2.73 0
"Chev. Nova"         3955 19 3 3.5 13 3430 197 43 250 2.56 0
"Dodge Colt"         3984 30 5   2  8 2120 163 35  98 3.54 0
"Dodge Diplomat"     4010 18 2   4 17 3600 206 46 318 2.47 0
"Dodge Magnum"       5886 16 2   4 17 3600 206 46 318 2.47 0
"Dodge St. Regis"    6342 17 2 4.5 21 3740 220 46 225 2.94 0
"Ford Fiesta"        4389 28 4 1.5  9 1800 147 33  98 3.15 0
"Ford Mustang"       4187 21 3   2 10 2650 179 43 140 3.08 0
"Linc. Continental" 11497 12 3 3.5 22 4840 233 51 400 2.47 0
"Linc. Mark V"      13594 12 3 2.5 18 4720 230 48 400 2.47 0
"Linc. Versailles"  13466 14 3 3.5 15 3830 201 41 302 2.47 0
"Merc. Bobcat"       3829 22 4   3  9 2580 169 39 140 2.73 0
"Merc. Cougar"       5379 14 4 3.5 16 4060 221 48 302 2.75 0
"Merc. Marquis"      6165 15 3 3.5 23 3720 212 44 302 2.26 0
"Merc. Monarch"      4516 18 3   3 15 3370 198 41 250 2.43 0
"Merc. XR-7"         6303 14 4   3 16 4130 217 45 302 2.75 0
"Merc. Zephyr"       3291 20 3 3.5 17 2830 195 43 140 3.08 0
"Olds 98"            8814 21 4   4 20 4060 220 43 350 2.41 0
"Olds Cutl Supr"     5172 19 3   2 16 3310 198 42 231 2.93 0
"Olds Cutlass"       4733 19 3 4.5 16 3300 198 42 231 2.93 0
"Olds Delta 88"      4890 18 4   4 20 3690 218 42 231 2.73 0
"Olds Omega"         4181 19 3 4.5 14 3370 200 43 231 3.08 0
"Olds Starfire"      4195 24 1   2 10 2730 180 40 151 2.73 0
"Olds Toronado"     10371 16 3 3.5 17 4030 206 43 350 2.41 0
"Plym. Arrow"        4647 28 3   2 11 3260 170 37 156 3.05 0
"Plym. Champ"        4425 34 5 2.5 11 1800 157 37  86 2.97 0
"Plym. Horizon"      4482 25 3   4 17 2200 165 36 105 3.37 0
"Plym. Sapporo"      6486 26 . 1.5  8 2520 182 38 119 3.54 0
"Plym. Volare"       4060 18 2   5 16 3330 201 44 225 3.23 0
"Pont. Catalina"     5798 18 4   4 20 3700 214 42 231 2.73 0
"Pont. Firebird"     4934 18 1 1.5  7 3470 198 42 231 3.08 0
"Pont. Grand Prix"   5222 19 3   2 16 3210 201 45 231 2.93 0
"Pont. Le Mans"      4723 19 3 3.5 17 3200 199 40 231 2.93 0
"Pont. Phoenix"      4424 19 . 3.5 13 3420 203 43 231 3.08 0
"Pont. Sunbird"      4172 24 2   2  7 2690 179 41 151 2.73 0
"Audi 5000"          9690 17 5   3 15 2830 189 37 131  3.2 1
"Audi Fox"           6295 23 3 2.5 11 2070 174 36  97  3.7 1
"BMW 320i"           9735 25 4 2.5 12 2650 177 34 121 3.64 1
"Datsun 200"         6229 23 4 1.5  6 2370 170 35 119 3.89 1
"Datsun 210"         4589 35 5   2  8 2020 165 32  85  3.7 1
"Datsun 510"         5079 24 4 2.5  8 2280 170 34 119 3.54 1
"Datsun 810"         8129 21 4 2.5  8 2750 184 38 146 3.55 1
"Fiat Strada"        4296 21 3 2.5 16 2130 161 36 105 3.37 1
"Honda Accord"       5799 25 5   3 10 2240 172 36 107 3.05 1
"Honda Civic"        4499 28 4 2.5  5 1760 149 34  91  3.3 1
"Mazda GLC"          3995 30 4 3.5 11 1980 154 33  86 3.73 1
"Peugeot 604"       12990 14 . 3.5 14 3420 192 38 163 3.58 1
"Renault Le Car"     3895 26 3   3 10 1830 142 34  79 3.72 1
"Subaru"             3798 35 5 2.5 11 2050 164 36  97 3.81 1
"Toyota Celica"      5899 18 5 2.5 14 2410 174 36 134 3.06 1
"Toyota Corolla"     3748 31 5   3  9 2200 165 35  97 3.21 1
"Toyota Corona"      5719 18 5   2 11 2670 175 36 134 3.05 1
"VW Dasher"          7140 23 4 2.5 12 2160 172 36  97 3.74 1
"VW Diesel"          5397 41 5   3 15 2040 155 35  90 3.78 1
"VW Rabbit"          4697 25 4   3 15 1930 155 35  89 3.78 1
"VW Scirocco"        6850 25 4   2 16 1990 156 36  97 3.78 1
"Volvo 260"         11995 17 5 2.5 14 3170 193 37 163 2.98 1
end
label values foreign origin
label def origin 0 "Domestic", modify
label def origin 1 "Foreign", modify

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

[Hemanshu Kumar]

I don't think this auto data illustrates what you need. Could you construct your own data, including illustrative values for the variable(s) you want to create, so we can get a better sense of precisely what you want?

[May Blake]

Hi Hemanshu, Thank you for your response. I have pasted an example of my dataset below.


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

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(age ethnic) float bmi byte anxiety str13(na creat) str6 k float mag str12 ads str13 dlco float cat_date byte(recruited recruitment_date depression Group)
67 1     . 0 ""             ""             ""         . ""             ""             . . . . 0
61 1     . 0 ""             ""             ""         . ""             ""             . . . . 0
59 1  22.6 0 "62.8"         "60.6"         "61.0"     . "20.7"         "377.7"        . . . . 0
56 1 30.26 0 "73.3"         "62.8"         "65.4"     . "27.7"         "505.5"        . . . . 0
66 1  24.2 0 "72.3"         "65.5"         "67.8"     . "27.1"         "494.5"        . . . . 0
81 1 22.81 0 "74.6"         "63.2"         "63.0"     . "25.3"         "461.7"        . . . . 0
63 1 34.03 0 "67.7"         "61.6"         "64.9"     . "26.2"         "478.1"        . . . . 0
67 1 20.53 0 "63.4"         "40.3"         "<40.0"    . "10.9"         "198.9"        . . . . 0
64 1    36 0 "71.9"         "65.0"         "67.2"     . "27.7"         "505.5"        . . . . 0
66 1  25.8 0 "57.7"         "41.9"         "85.7"     . "30.5"         "556.6"        . . . . 0
61 1     . 0 ""             ""             ""         . ""             ""             . . . . 0
67 1 28.25 0 "69.4"         "62.0"         "67.4"     . "23.2"         "423.4"        . . . . 0
74 1 27.98 0 "72.5"         "59.1"         "59.4"     . "20.9"         "381.4"        . . . . 0
86 1  24.4 0 "66.0"         "59.9"         "62.1"     . "19.9"         "363.1"        . . . . 0
74 1 20.78 0 "75.0"         "59.5"         "59.9"     . "20.8"         "379.6"        . . . . 0
51 1 27.31 0 "70.2"         "60.1"         "62.3"     . "21.5"         "392.3"        . . . . 0
57 1 37.58 0 "72.5"         "62.1"         "64.3"     . "27.8"         "507.3"        . . . . 0
67 1    26 0 "71.4"         "59.1"         "61.0"   6.1 "19.6"         "357.7"        . . . . 1
66 1 31.97 0 "42.0"         "<40.0"        "<40.0"    . "4.1"          "135.3"        . . . . 1
83 1 24.69 0 "81.3"         "73.3"         ">75.0"  5.7 ">52.0"        "inconclusive" . . . . 1
59 1 33.25 0 "79.7"         "74.5"         "74.0"     . ">52.0"        "inconclusive" . . . . 1
81 1  21.3 0 ""             ""             ""         . ""             ""             . . . . 1
73 1  25.1 0 "70.5"         "62.4"         "64.7"     . "24.1"         "439.8"        . . . . 1
88 1 30.65 0 "39.0"         "48.6"         "60.4"   8.2 "18.6"         "339.4"        . . . . 1
58 1 25.38 0 "77.8"         "68.3"         "73.3"   6.7 "37.6"         "686.1"        . . . . 1
85 1 21.06 0 "62.0"         "61.4"         "63.2"   6.7 "23.1"         "421.5"        . . . . 1
68 1 40.29 0 "81.6"         "74.3"         ">75.0"    4 ">52.0"        "inconclusive" . . . . 1
71 1 25.84 0 "73.8"         "64.1"         "66.4"   5.3 "26.2"         "478.1"        . . . . 1
81 1 29.18 0 "76.5"         "63.5"         "63.7"   3.2 "22.7"         "414.2"        . . . . 1
76 0 24.96 0 "77.7"         "69.9"         "73.1"     . "49.8"         "908.8"        . . . . 1
62 1 28.51 0 "71.8"         "60.7"         "63.3"     . "21.1"         "385.0"        . . . . 1
55 1 28.48 0 "75.6"         "66.9"         "67.6"   4.9 "29.4"         "536.5"        . . . . 1
74 1    26 0 "74.2"         "65.1"         "66.7"   4.5 "24.6"         "448.9"        . . . . 1
76 1 21.63 0 "75.2"         "61.0"         "62.2"   5.1 "18.6"         "339.4"        . . . . 1
75 1 27.01 0 "39.0"         "<40.0"        "<40.0"    . "12.6"         "229.9"        . . . . 1
67 1 31.28 1 "76.1"         "65.1"         "65.8"     . "28.6"         "521.9"        . . . . 1
78 1 19.45 0 "73.4"         "63.8"         "67.7"   6.2 "31.0"         "565.7"        . . . . 1
82 1  19.3 0 "74.1"         "66.0"         "68.0"     . "31.1"         "567.5"        . . . . 1
79 1 33.13 0 "65.7"         "57.8"         "58.2"     . "18.5"         "337.6"        . . . . 1
62 1 27.98 0 "70.6"         "58.3"         "58.8"     . "17.9"         "326.6"        . . . . 1
82 1 17.56 0 "61.6"         "64.4"         "66.9"     . "30.8"         "562.0"        . . . . 1
50 1 20.54 0 "77.0"         "67.8"         "69.9"     6 "38.7"         "706.2"        . . . . 1
73 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
69 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
79 1 22.71 0 "64.0"         "40.1"         "40.6"     . "9.4"          "171.5"        . . . . 1
69 0 28.83 0 "48.0"         "45.1"         "53.9"     . "19.0"         "346.7"        . . . . 1
64 1 27.98 0 "77.4"         "68.3"         "69.4"     . "43.3"         "790.1"        . . . . 1
68 1 24.71 0 "69.5"         "49.5"         "47.2"   3.1 "19.2"         "350.4"        . . . . 1
62 1 28.04 0 "72.1"         "60.1"         "63.6"   6.1 "22.0"         "401.5"        . . . . 1
65 0 26.67 0 "80.1"         "70.5"         "71.2"   4.7 "49.7"         "906.9"        . . . . 1
65 1  22.7 0 "72.7"         "68.0"         "69.0"     . "35.3"         "644.2"        . . . . 1
76 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
90 1 24.41 0 "69.3"         "<40.0"        "<40.0"    . "16.5"         "301.1"        . . . . 1
67 1 25.22 0 "73.0"         "58.4"         "58.2"   2.8 "17.7"         "323.0"        . . . . 1
60 1 37.68 0 "74.8"         "61.5"         "65.1"   6.4 "20.4"         "372.3"        . . . . 1
66 1  22.5 0 "71.8"         "65.7"         "66.7"   6.3 "23.3"         "425.2"        . . . . 1
60 1 36.59 0 "70.0"         "54.3"         "51.1"     . "16.2"         "295.6"        . . . . 1
63 1 28.89 0 "77.1"         "62.6"         "63.8"     4 "20.6"         "375.9"        . . . . 1
64 1  38.4 0 "70.0"         "61.4"         "61.9"     . "19.8"         "361.3"        . . . . 1
42 0 36.69 0 "58.7"         "43.5"         "<40.0"    . "13.0"         "237.2"        . . . . 1
61 1 34.49 0 "56.5"         "50.8"         "61.9"     . "19.1"         "348.5"        . . . . 1
69 1 20.81 0 "70.3"         "59.4"         "61.3"     . "20.3"         "370.4"        . . . . 1
79 1 30.23 0 "62.9"         "52.9"         "55.9"   4.6 "16.7"         "304.7"        . . . . 1
81 1    30 0 "76.3"         "64.2"         "64.7"   3.9 "22.3"         "406.9"        . . . . 1
55 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
64 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
71 1 31.77 0 "inconclusive" "inconclusive" "<40.0"    . "inconclusive" "inconclusive" . . . . 1
84 1  21.3 0 "75.5"         "63.0"         "66.2"   5.2 "27.5"         "501.8"        . . . . 1
86 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
54 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
67 1 29.75 0 "72.7"         "59.3"         "60.7"   5.8 "21.0"         "383.2"        . . . . 1
67 1     . 0 ""             ""             ""         . ""             ""             . . . . 1
73 1 26.46 0 "75.0"         "64.0"         "65.2"   5.3 "21.8"         "397.8"        . . . . 1
64 1 22.89 0 "72.7"         "63.3"         "63.8"   8.4 "20.7"         "377.7"        . . . . 1
67 1     . 0 ""             ""             ""         . ""             ""             . . . . 2
52 1 28.29 0 "71.3"         "61.9"         "65.6"     . "26.8"         "489.1"        . . . . 2
74 1 23.18 0 "68.5"         "56.5"         "56.7"     . "14.2"         "259.1"        . . . . 2
66 1 22.64 0 "79.8"         "69.7"         "71.3"     . "44.7"         "815.7"        . . . . 2
57 1 49.82 0 "78.8"         "68.5"         "70.6"   6.3 "44.0"         "802.9"        . . . . 2
72 1 27.32 0 "74.4"         "60.6"         "61.2"   5.2 "19.3"         "352.2"        . . . . 2
69 1 18.71 0 "66.2"         "55.0"         "58.8"   6.9 "20.0"         "365.0"        . . . . 2
74 1 27.06 0 "76.3"         "63.6"         "64.5"     . "27.6"         "503.6"        . 0 . 0 2
64 1 25.35 0 "70.2"         "61.3"         "65.2"     . "27.1"         "494.5"        . 0 . 1 2
66 1 33.42 0 "74.6"         "60.3"         "61.6"     . "21.9"         "399.6"        . 0 . 0 2
73 1 27.75 0 ""             ""             ""         . ""             ""             . . . . 2
68 1 28.48 0 "82.2"         ">75.0"        "75.0"   4.2 ">52.0"        "inconclusive" . . . . 2
68 1 29.75 0 "72.3"         "57.8"         "62.0"     . "23.5"         "428.8"        . 0 . 0 2
61 1 27.26 0 "73.9"         "64.3"         "65.8"   7.2 "27.3"         "498.2"        . . . . 2
68 1 31.75 1 "75.2"         "64.7"         "64"       5 "18.8"         "343.1"        . . . . 2
78 1 30.79 0 ""             ""             ""         . ""             ""             . 0 . 1 2
37 1  22.3 0 "39.0"         "<40.0"        "< 40.0"   . "4.4"          "140.5"        . . . . 2
81 1 22.31 0 "40.0"         "<40.0"        "<40.0"    . "7.1"          "159.0"        . . . . 2
75 1     . 0 ""             ""             ""         . ""             ""             . . . . 2
67 1 29.35 1 "72.7"         "58.8"         "61.1"     . "20.9"         "381.4"        . 0 . 0 2
64 1  29.7 0 "65.0"         "57.4"         "58.8"     . "16.2"         "295.6"        . . . . 2
84 1 31.52 0 "72.2"         "63.6"         "65.5"     . "29.7"         "542.0"        . 0 . 1 2
end
format %dM_d,_CY cat_date

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

Listed 96 out of 96 observations

[Hemanshu Kumar]

Thanks for the data extract, May. Now can you tell us exactly what you want in terms of this data, preferably even show us what values the new variables will take for each of these rows?

[May Blake]

In the variable marked "Group", I have 3 groups. I am trying to create 3 new columns for each group. Lets call them group 1, group 2, and group 3. Then I want to create and flag a variable list where a group is flagged as Yes if observations appear or no if all observations are missing for that variable/group combination. I want them marked "Yes" despite some missing observations but solely based on the group, i.e. if any observations appear for the group, then it is Yes. My data has about 300 variables so I need a smart way to query the list. Below is the excel format I am aiming for.

Variable	Group 1	Group 2	Group 3
age	Yes	Yes	Yes
ethnic	Yes	Yes	Yes
bmi	Yes	Yes	Yes
anxiety	Yes	Yes	Yes
na	Yes	No	Yes
creat	No	Yes	Yes
ads	No	No	Yes

[Hemanshu Kumar]

Hmm, perhaps this is close to what you want:

foreach var of varlist age-depression {
    gen byte nm_`var' = !missing(`var')
}

collapse (max) nm_*, by(Group)
rename nm_* *
xpose , clear varname
rename v# Group#
drop if _varname == "Group"
order _varname
label define YESNO 0 "No" 1 "Yes"
label values Group* YESNO

which produces:

. list, noobs sep(0)

  +---------------------------------------------+
  |         _varname   Group1   Group2   Group3 |
  |---------------------------------------------|
  |              age      Yes      Yes      Yes |
  |           ethnic      Yes      Yes      Yes |
  |              bmi      Yes      Yes      Yes |
  |          anxiety      Yes      Yes      Yes |
  |               na      Yes      Yes      Yes |
  |            creat      Yes      Yes      Yes |
  |                k      Yes      Yes      Yes |
  |              mag       No      Yes      Yes |
  |              ads      Yes      Yes      Yes |
  |             dlco      Yes      Yes      Yes |
  |         cat_date       No       No       No |
  |        recruited       No       No      Yes |
  | recruitment_date       No       No       No |
  |       depression       No       No      Yes |
  +---------------------------------------------+

[May Blake]

Hi Hemanshu, this worked wonderfully! Exactly what I needed. Thank you for the prompt and accurate assistance.