Dropping observation under conditions

[Philipp Grab]

Dear expers,

my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double facilityid str76 lender double bankallocation str26 lenderrole
  1 "General Electric Capital Corp"               100 "Lead bank"  
  2 "General Electric Capital Corp"               100 "Lead bank"  
  3 "General Electric Capital Corp"               100 "Lead bank"  
  4 "General Electric Capital Corp"               100 "Lead bank"  
  5 "Fleet Real Estate"                           100 "Lead bank"  
  6 "Toronto Dominion Bank"                       100 "Lead bank"  
  7 "Toronto Dominion Bank"                       100 "Lead bank"  
  8 "Toronto Dominion Bank"                       100 "Lead bank"  
  9 "Bank of Boston"                              100 "Lead bank"  
 10 "Manufacturers Hanover Trust"                 100 "Lead bank"  
 12 "Westpac Banking Corp"                        100 "Lead bank"  
 13 "Capital Bank NA"                             100 "Lead bank"  
 14 "First American Bank & Trust"                 100 "Lead bank"  
 15 "Security Pacific National Bank"              100 "Lead bank"  
 16 "Banque Worms SA"                             100 "Lead bank"  
 17 "National Westminster Bank USA"               100 "Lead bank"  
 18 "National Westminster Bank USA"               100 "Lead bank"  
 21 "Pacificorp Credit"                           100 "Lead bank"  
 22 "Pacificorp Credit"                           100 "Lead bank"  
 26 "First National Bank Minneapolis"             100 "Lead bank"  
 28 "Heller Financial Inc"                        100 "Lead bank"  
 29 "Heller Financial Inc"                        100 "Lead bank"  
 30 "Bank of Boston"                              100 "Lead bank"  
 39 "Prudential Insurance Co of America"          100 "Lead bank"  
 40 "LF Rothschild & Co Inc"                      100 "Lead bank"  
 41 "Citibank"                                    100 "Agent"      
 42 "Citibank"                                    100 "Agent"      
 43 "Citibank"                                    100 "Agent"      
 44 "Dominion Bank"                                17 "Participant"
 44 "PNC Bank - Cincinnati"                        17 "Participant"
 44 "Commerce Union Bank"                          26 "Participant"
 44 "Third National Bank of Nashville"             40 "Agent"      
 45 "Chemical Bank"                               100 "Agent"      
 46 "Chemical Bank"                               100 "Agent"      
 47 "Chemical Bank"                               100 "Agent"      
 48 "Union Bank of Norway"                        100 "Lead bank"  
 49 "Security Pacific National Bank"              100 "Lead bank"  
 50 "Security Pacific National Bank"              100 "Lead bank"  
 51 "Security Pacific National Bank"              100 "Lead bank"  
 54 "Bank of Boston"                              100 "Lead bank"  
 55 "Dresdner Bank AG"                            4.6 "Participant"
 55 "National Westminster Bank USA"              32.4 "Agent"      
 55 "Banca di Roma SpA"                          1.85 "Participant"
 55 "Arab Banking Corp BSC [ABC]"                 5.5 "Participant"
 55 "Bank of Nova Scotia"                        18.5 "Participant"
 55 "National Bank of Canada"                    18.5 "Participant"
 55 "Creditanstalt Bankverein"                   18.5 "Participant"
 56 "1st Source Bank"                            53.3 "Agent"      
 56 "Harris Trust & Savings Bank"                46.7 "Co-agent"   
 58 "Yasuda Trust & Banking Co Ltd"              3.33 "Participant"
 58 "US National Bank of Oregon"                 6.67 "Participant"
 58 "Societe Generale SA"                          10 "Participant"
 58 "Security Pacific National Bank"               20 "Agent"      
 58 "Le Credit Lyonnais SA [LCL]"                6.67 "Participant"
 58 "Long-Term Credit Bank of Japan Ltd"           10 "Participant"
 58 "Continental Bank"                          13.33 "Participant"
 58 "Chemical Bank"                             13.33 "Participant"
 58 "First Interstate Bank of Nevada"            6.67 "Participant"
 58 "Midlantic National Bank"                      10 "Participant"
 59 "Continental Bank"                          13.33 "Participant"
 59 "US National Bank of Oregon"                 6.67 "Participant"
 59 "Chemical Bank"                             13.33 "Participant"
 59 "First Interstate Bank of Nevada"            6.67 "Participant"
 59 "Long-Term Credit Bank of Japan Ltd"           10 "Participant"
 59 "Yasuda Trust & Banking Co Ltd"              3.33 "Participant"
 59 "Societe Generale SA"                          10 "Participant"
 59 "Security Pacific National Bank"               20 "Agent"      
 59 "Le Credit Lyonnais SA [LCL]"                6.67 "Participant"
 59 "Midlantic National Bank"                      10 "Participant"
 60 "Yasuda Trust & Banking Co Ltd"              3.33 "Participant"
 60 "First Interstate Bank of Nevada"            6.67 "Participant"
 60 "Long-Term Credit Bank of Japan Ltd"           10 "Participant"
 60 "Midlantic National Bank"                      10 "Participant"
 60 "Continental Bank"                          13.33 "Participant"
 60 "Security Pacific National Bank"               20 "Agent"      
 60 "US National Bank of Oregon"                 6.67 "Participant"
 60 "Chemical Bank"                             13.33 "Participant"
 60 "Le Credit Lyonnais SA [LCL]"                6.67 "Participant"
 60 "Societe Generale SA"                          10 "Participant"
 62 "Generale Bank SA"                              7 "Participant"
 62 "First Chicago"                                 9 "Participant"
 62 "National Westminster Bank Plc"                 7 "Participant"
 62 "Citizens & Southern National Bank"             7 "Participant"
 62 "National City Bank"                            7 "Participant"
 62 "Harris Trust & Savings Bank"                   9 "Participant"
 62 "Credit Suisse AG"                             12 "Agent"      
 62 "National Bank of Canada"                       7 "Participant"
 62 "Security Pacific National Bank"                7 "Participant"
 62 "NationsBank of North Carolina"                 7 "Participant"
 62 "Westpac Banking Corp"                          7 "Participant"
 62 "Commerzbank AG"                                7 "Participant"
 62 "Bank of Nova Scotia"                           7 "Participant"
 64 "Continental Bank"                           22.2 "Participant"
 64 "Home Federal Savings"                       11.1 "Participant"
 64 "National Westminster Bank USA"              13.9 "Participant"
 64 "National Bank of Canada"                    11.1 "Participant"
 64 "Bank of Boston"                             30.5 "Agent"      
 64 "Bankers Trust Co"                           11.1 "Participant"
 67 "Boatmen's National Bank of Kansas City"      100 "Lead bank"  
 68 "Vermont National Bank"                       100 "Lead bank"  
 69 "MBank Venture Capital"                      16.7 "Participant"
 69 "Interfirst Venture Corp"                    16.7 "Participant"
 69 "Bank of Boston"                             66.7 "Agent"      
 70 "Citicorp Industrial Credit"                  100 "Lead bank"  
 71 "Citicorp Industrial Credit"                  100 "Lead bank"  
 74 "Chemical Bank"                               100 "Lead bank"  
 75 "Security Pacific National Bank"              100 "Lead bank"  
 76 "Chemical Bank"                              83.3 "Agent"      
 76 "City National Bank Beverly Hills"           16.7 "Participant"
 77 "City National Bank Beverly Hills"           16.7 "Participant"
 77 "Chemical Bank"                              83.3 "Agent"      
 79 "Algemene Bank Nederland"                     100 "Lead bank"  
 80 "Bank of California"                          100 "Lead bank"  
 81 "WestAmerica Bancorporation"                  100 "Lead bank"  
 82 "Mellon Bank"                                 100 "Lead bank"  
 84 "Swiss Bank Corp"                             100 "Agent"      
 85 "Midlantic National Bank"                     100 "Lead bank"  
 86 "First Interstate Bank of California"         100 "Lead bank"  
 87 "Marine Midland Bank"                         100 "Lead bank"  
 88 "Citibank"                                    100 "Lead bank"  
 89 "Citibank"                                    100 "Agent"      
 90 "Citibank"                                    100 "Lead bank"  
 91 "Citibank"                                    100 "Lead bank"  
 96 "Toronto Dominion Bank"                       100 "Agent"      
 97 "Toronto Dominion Bank"                       100 "Agent"      
 98 "Citicorp Industrial Credit"                   17 "Participant"
 98 "Citibank"                                     33 "Participant"
 98 "Bankers Trust Co"                             50 "Agent"      
 99 "Citicorp Industrial Credit"                   17 "Participant"
 99 "Citibank"                                     33 "Participant"
 99 "Bankers Trust Co"                             50 "Agent"      
100 "Citibank"                                     33 "Participant"
100 "Bankers Trust Co"                             50 "Agent"      
100 "Citicorp Industrial Credit"                   17 "Participant"
102 "Royal Bank of Canada"                        100 "Lead bank"  
103 "Royal Bank of Canada"                        100 "Lead bank"  
104 "Royal Bank of Canada"                        100 "Lead bank"  
105 "Marine Bank"                                  35 "Agent"      
106 "Marine Bank"                                  35 "Agent"      
107 "Chase Manhattan Bank"                        100 "Agent"      
108 "Sovran Bank"                                 100 "Lead bank"  
109 "National Westminster Bank USA"             17.65 "Co-agent"   
109 "Midland Bank Plc"                            5.8 "Participant"
109 "Bankers Trust Co"                           36.5 "Co-agent"   
109 "First National Bank of Maryland"             3.5 "Participant"
109 "Fleet National Bank"                         5.8 "Participant"
109 "Creditanstalt Bankverein"                    8.2 "Participant"
109 "Kleinwort Benson"                          4.725 "Participant"
109 "Heller Financial Inc"                      17.65 "Participant"
110 "Heller Financial Inc"                      17.65 "Participant"
110 "Midland Bank Plc"                            5.8 "Participant"
110 "Creditanstalt Bankverein"                    8.2 "Participant"
110 "National Westminster Bank USA"             17.65 "Co-agent"   
110 "Fleet National Bank"                         5.8 "Participant"
110 "Kleinwort Benson"                          4.725 "Participant"
110 "Bankers Trust Co"                           36.5 "Co-agent"   
110 "First National Bank of Maryland"             3.5 "Participant"
111 "Security Pacific National Bank"              100 "Agent"      
112 "Swiss Bank Corp"                           11.65 "Participant"
112 "Bank of Nova Scotia"                        6.84 "Participant"
112 "Security Pacific National Bank"             6.84 "Participant"
112 "CIBC [Canadian Imperial Bank of Commerce]"  6.84 "Participant"
112 "Manufacturers Hanover Trust"               19.17 "Agent"      
112 "Nippon Credit Bank Ltd"                     6.84 "Participant"
112 "Credit Agricole SA"                        11.65 "Participant"
112 "First National Bank Minneapolis"           11.65 "Participant"
112 "National Westminster Bank USA"             11.65 "Participant"
112 "Long-Term Credit Bank of Japan Ltd"         6.84 "Participant"
113 "CIBC [Canadian Imperial Bank of Commerce]"  6.84 "Participant"
113 "Credit Agricole SA"                        11.65 "Participant"
113 "Swiss Bank Corp"                           11.65 "Participant"
113 "Security Pacific National Bank"             6.84 "Participant"
113 "Long-Term Credit Bank of Japan Ltd"         6.84 "Participant"
113 "Nippon Credit Bank Ltd"                     6.84 "Participant"
113 "Bank of Nova Scotia"                        6.84 "Participant"
113 "National Westminster Bank USA"             11.65 "Participant"
113 "First National Bank Minneapolis"           11.65 "Participant"
113 "Manufacturers Hanover Trust"               19.17 "Agent"      
114 "Long-Term Credit Bank of Japan Ltd"         6.84 "Participant"
114 "Manufacturers Hanover Trust"               19.17 "Agent"      
114 "CIBC [Canadian Imperial Bank of Commerce]"  6.84 "Participant"
114 "Bank of Nova Scotia"                        6.84 "Participant"
114 "National Westminster Bank USA"             11.65 "Participant"
114 "Credit Agricole SA"                        11.65 "Participant"
114 "Swiss Bank Corp"                           11.65 "Participant"
114 "First National Bank Minneapolis"           11.65 "Participant"
114 "Security Pacific National Bank"             6.84 "Participant"
114 "Nippon Credit Bank Ltd"                     6.84 "Participant"
115 "National Westminster Bank USA"             34.19 "Lead bank"  
115 "United States Trust"                        5.13 "Participant"
115 "Irving Trust"                               8.55 "Participant"
115 "First Interstate Bank of California"       25.64 "Participant"
115 "Connecticut Bank & Trust"                  13.68 "Participant"
115 "Horizon Bank"                              12.82 "Participant"
116 "Horizon Bank"                              12.82 "Participant"
116 "Irving Trust"                               8.55 "Participant"
116 "First Interstate Bank of California"       25.64 "Participant"
116 "United States Trust"                        5.13 "Participant"
116 "Connecticut Bank & Trust"                  13.68 "Participant"
116 "National Westminster Bank USA"             34.19 "Lead bank"  
end

My goal is to have one observation per facilityid and in cases where there are more than one observation per facilityid, I want that stata only takes the one observation where the bank is the leader in the facility (drop all participant). The problem is, that sometimes there is different naming of the leader in the facility (Lead bank or Lead Agent) and sometimes there is even a Lead Co-Agent where two banks share the role. In these cases I want to have the one with the higher allocation (variable bankallocation). Is this possible?

[Johannes Pimpl]

Is bankallocation always the lowest for lenderrole "participant"?

In that case you can order by facilityid and bankallocation (in descending order) and drop all duplicates:

gsort facilityid -bankallocation
quietly by facilityid: g dup = cond(_N==1,0,_n)
drop if dup > 1
drop dup

[Nick Cox]

keep if word(lenderrole, 1) == "Lead" 

assert !missing(bankallocation) 

bysort facilityid (bankallocation) : keep if _n == _N

The last line of code would give incorrect results if missing values were present, so I inserted a line earlier to check.

If you have missing values, drop those observations first.

[Philipp Grab]

keep if word(lenderrole, 1) == "Lead"

assert !missing(bankallocation)

bysort facilityid (bankallocation) : keep if _n == _N

The last line of code would give incorrect results if missing values were present, so I inserted a line earlier to check.

If you have missing values, drop those observations first.

Thanks to you both. Helped a lot!
My data now looks like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(facilityid packageid) str76 lender double(bankallocation facilityamt)
  1 100 "General Electric Capital Corp"                     100   7000000
  2 100 "General Electric Capital Corp"                     100  10000000
  3 100 "General Electric Capital Corp"                     100  13000000
  4 100 "General Electric Capital Corp"                     100  20000000
  5 101 "Fleet Real Estate"                                 100  18000000
  6 102 "Toronto Dominion Bank"                             100  60000000
  7 102 "Toronto Dominion Bank"                             100 1.450e+08
  8 102 "Toronto Dominion Bank"                             100 1.650e+08
  9 103 "Bank of Boston"                                    100  10000000
 10 104 "Manufacturers Hanover Trust"                       100  10000000
 12 106 "Westpac Banking Corp"                              100  10000000
 13 107 "Capital Bank NA"                                   100  10000000
 14 108 "First American Bank & Trust"                       100  15400000
 15 109 "Security Pacific National Bank"                    100    600000
 16 110 "Banque Worms SA"                                   100   1300000
 17 111 "National Westminster Bank USA"                     100  45000000
 18 111 "National Westminster Bank USA"                     100  35000000
 21 113 "Pacificorp Credit"                                 100  11500000
 22 113 "Pacificorp Credit"                                 100   6500000
 26 116 "First National Bank Minneapolis"                   100  14550000
 28 117 "Heller Financial Inc"                              100  11000000
 29 117 "Heller Financial Inc"                              100  41000000
 30 118 "Bank of Boston"                                    100  16000000
 39 120 "Prudential Insurance Co of America"                100   5000000
 40 121 "LF Rothschild & Co Inc"                            100  65000000
 41 122 "Citibank"                                          100   5000000
 42 122 "Citibank"                                          100   5000000
 43 122 "Citibank"                                          100  51000000
 44 123 "Third National Bank of Nashville"                   40  88822000
 45 124 "Chemical Bank"                                     100  34000000
 46 124 "Chemical Bank"                                     100  67000000
 47 124 "Chemical Bank"                                     100   3000000
 48 125 "Union Bank of Norway"                              100 1.100e+08
 49 126 "Security Pacific National Bank"                    100   2000000
 50 126 "Security Pacific National Bank"                    100   3500000
 51 126 "Security Pacific National Bank"                    100  28000000
 54 128 "Bank of Boston"                                    100  42000000
 55 129 "National Westminster Bank USA"                    32.4 5.400e+08
 56 130 "1st Source Bank"                                  53.3  15000000
 58 131 "Security Pacific National Bank"                     20  40000000
 59 131 "Security Pacific National Bank"                     20 1.500e+08
 60 131 "Security Pacific National Bank"                     20 1.000e+08
 62 133 "Credit Suisse AG"                                   12 5.000e+08
 64 134 "Bank of Boston"                                   30.5 1.800e+08
 67 135 "Boatmen's National Bank of Kansas City"            100    240000
 68 136 "Vermont National Bank"                             100   1750000
 69 137 "Bank of Boston"                                   66.7   6000000
 70 138 "Citicorp Industrial Credit"                        100   5000000
 71 138 "Citicorp Industrial Credit"                        100  10000000
 74 140 "Chemical Bank"                                     100   2600000
 75 141 "Security Pacific National Bank"                    100   2600000
 76 142 "Chemical Bank"                                    83.3  48000000
 77 142 "Chemical Bank"                                    83.3 1.020e+08
 79 143 "Algemene Bank Nederland"                           100   2000000
 80 144 "Bank of California"                                100   2000000
 81 145 "WestAmerica Bancorporation"                        100   2500000
 82 146 "Mellon Bank"                                       100  12250000
 84 148 "Swiss Bank Corp"                                   100 4.000e+08
 85 149 "Midlantic National Bank"                           100    300000
 86 150 "First Interstate Bank of California"               100   5000000
 87 151 "Marine Midland Bank"                               100   5000000
 88 152 "Citibank"                                          100  10000000
 89 153 "Citibank"                                          100  19000000
 90 153 "Citibank"                                          100  20000000
 91 153 "Citibank"                                          100  45000000
 96 155 "Toronto Dominion Bank"                             100  65000000
 97 155 "Toronto Dominion Bank"                             100  10000000
 98 156 "Bankers Trust Co"                                   50   5000000
 99 156 "Bankers Trust Co"                                   50   5000000
100 156 "Bankers Trust Co"                                   50  40000000
102 157 "Royal Bank of Canada"                              100   5000000
103 158 "Royal Bank of Canada"                              100    500000
104 158 "Royal Bank of Canada"                              100  10000000
105 159 "Marine Bank"                                        35  10000000
106 159 "Marine Bank"                                        35  25000000
107 160 "Chase Manhattan Bank"                              100 5.000e+08
108 161 "Sovran Bank"                                       100   4000000
111 163 "Security Pacific National Bank"                    100  45000000
112 164 "Manufacturers Hanover Trust"                     19.17 2.650e+08
113 164 "Manufacturers Hanover Trust"                     19.17 2.500e+08
114 164 "Manufacturers Hanover Trust"                     19.17 1.000e+08
115 165 "National Westminster Bank USA"                   34.19   7500000
116 165 "National Westminster Bank USA"                   34.19  48500000
117 166 "BAII Banking Corp"                                  28 1.000e+08
118 167 "First Chicago"                                      33  15000000
119 168 "CIBC [Canadian Imperial Bank of Commerce]"         100  80000000
120 168 "CIBC [Canadian Imperial Bank of Commerce]"         100  25000000
121 169 "First Chicago"                                     8.3 3.000e+08
122 170 "First Chicago"                                      25  40000000
123 170 "First Chicago"                                      25 1.000e+08
124 171 "PNC Bank - Pittsburgh"                             100 1.350e+08
126 172 "Morgan Guaranty Trust"                              12 2.500e+08
127 173 "Chase Manhattan Bank"                              100 1.000e+08
128 174 "Imperial Bank"                                     100  18000000
129 175 "Toronto Dominion Bank"                             100 3.000e+08
130 176 "Norwest Bank"                                      100  20000000
131 176 "Norwest Bank"                                      100  15000000
132 177 "Boatmen's National Bank of St Louis"               100    750000
133 177 "Boatmen's National Bank of St Louis"               100   1450000
134 178 "Bankers Trust Co"                                15.75 2.600e+08
135 178 "Bankers Trust Co"                                15.75 1.200e+08
141 182 "Manufacturers National Bank"                        30  35000000
143 182 "Manufacturers National Bank"                        30  25000000
149 184 "Swiss Bank Corp"                                  11.7 1.500e+08
151 185 "Morgan Guaranty Trust"                           10.73 1.025e+09
157 188 "Banco Popular de Puerto Rico"                      100  10000000
160 190 "Bank of New York"                                 52.1 1.700e+08
161 190 "Bank of New York"                                 52.1  70000000
162 191 "Mutual Life Insurance Co of NY"                    100   5200000
165 193 "Mellon Bank"                                        40  40000000
166 194 "Fidelity Bank NA"                                  100   5000000
167 195 "Bank of Nova Scotia"                               100  24429967
168 196 "Royal Bank of Pennsylvania"                        100   2000000
171 198 "Toronto Dominion Bank"                              30 3.000e+08
173 199 "Toronto Dominion Bank"                              50  80000000
175 201 "Bank of America"                                    50  40000000
190 209 "Manufacturers Hanover Trust"                      33.3 1.500e+08
191 209 "Manufacturers Hanover Trust"                      33.3 1.500e+08
192 210 "Michigan National Bank"                            100   6000000
193 210 "Michigan National Bank"                            100   2500000
199 213 "Citibank"                                           10 3.000e+08
200 214 "Chase Manhattan Bank"                              100  13144680
201 214 "Chase Manhattan Bank"                              100   5000000
203 216 "Union Bank NA"                                     100    350000
204 247 "Colorado National Bank"                            100   3000000
205 248 "Security Pacific National Bank"                     28 2.500e+08
206 249 "DZ Bank AG [Ex-Deutsche Genissenschaftsbank AG]"   100   2500000
207 250 "Manufacturers Hanover Trust"                        40 1.600e+08
208 250 "Manufacturers Hanover Trust"                        40  12000000
218 254 "National Westminster Bank USA"                     100   5000000
219 255 "Morgan Guaranty Trust"                             7.2 5.750e+08
220 255 "Morgan Guaranty Trust"                             7.2 1.750e+08
225 220 "City National Bank Beverly Hills"                  100    150000
226 221 "PNC Bank - Cincinnati"                             100   5000000
228 222 "Citicorp Real Estate"                              100  14400000
229 223 "Marine Bank"                                       100   1500000
231 225 "Buckeye Union Insurance"                           100  10000000
233 227 "NationsBank of North Carolina"                     100   5600000
236 229 "Southeast Bank"                                    100   6000000
237 230 "National Westminster Bank USA"                     100   6750000
240 232 "Chemical Bank"                                    7.85 9.865e+08
250 235 "Great American First Savings Bank"                 100   5500000
252 237 "MBank Fort Worth"                                  100   6000000
253 237 "MBank Fort Worth"                                  100   6000000
254 237 "MBank Fort Worth"                                  100   6000000
255 237 "MBank Fort Worth"                                  100  12000000
256 237 "MBank Fort Worth"                                  100  12000000
257 238 "Eagle National Bank"                               100    250000
258 239 "NationsBank of North Carolina"                     100  11500000
261 241 "First Interstate Bank of Arizona"                   25 1.000e+08
263 242 "Continental Bank"                                 33.3  30000000
264 243 "Mellon Bank"                                     32.14 1.400e+08
266 244 "Bankers Trust Co"                                   12 2.500e+08
267 245 "CIBC [Canadian Imperial Bank of Commerce]"          45 1.000e+08
268 245 "CIBC [Canadian Imperial Bank of Commerce]"          45  75000000
269 246 "National Bank of Canada"                           100  65000000
275 258 "J Henry Schroder Wagg & Co Ltd"                    100   1850000
278 260 "Bankers Trust Co"                                  9.6 1.500e+09
279 261 "Philadelphia National Bank"                         40  24000000
280 261 "Philadelphia National Bank"                         40  18000000
285 265 "BayBank Middlesex"                                 100    600000
286 266 "Union Bank NA"                                     100   5000000
287 267 "Bank of America"                                   100   5000000
288 268 "Texas American Bank Midland"                       100   1400000
289 269 "Chemical Bank"                                      42  50000000
290 269 "Chemical Bank"                                     100   3557400
291 270 "Bank of Boston"                                   55.5  45000000
293 272 "United Bank of Arizona"                            100  12000000
294 273 "Sanwa Bank California"                             100   1200000
306 278 "Bank of America"                                  65.6   7775416
307 278 "Bank of America"                                  65.6   7775416
309 280 "Chemical Bank"                                   37.46 2.750e+08
310 281 "Morgan Guaranty Trust"                              20  50000000
311 282 "First City Texas - Midland"                       33.3   1500000
313 284 "Bankers Trust Co"                                41.94  12400000
314 285 "Continental Bank"                                 66.7 6.000e+08
316 287 "PNC Bank - Cincinnati"                             100  10000000
317 288 "Chase Manhattan Bank"                            43.75 1.750e+08
319 290 "Wells Fargo Bank"                                   40  50000000
320 290 "Wells Fargo Bank"                                   60  10000000
324 292 "Chase Manhattan Bank"                               24  75000000
329 297 "Michigan National Bank"                            100   6000000
330 297 "Michigan National Bank"                            100   2500000
331 298 "Bank of New York"                                39.51  81000000
332 298 "Bank of New York"                                39.51  70000000
334 300 "Allied Bank of Texas Houston"                      100   4000000
335 301 "Chemical Bank"                                      30 2.000e+08
337 303 "First Fidelity Bank NA New Jersey"                 100  50000000
338 304 "First Fidelity Bank NA New Jersey"                 100  80000000
339 305 "Perpetual Savings Bank"                            100    300000
340 306 "Signet Bank Maryland"                              100   3000000
342 308 "Mellon Bank"                                       100  15000000
345 310 "Chase Manhattan Bank"                            30.77  50000000
346 310 "Chase Manhattan Bank"                            30.77  24500000
347 311 "Bankers Trust Co"                                 26.9 4.488e+08
350 312 "Chemical Bank"                                     100  30000000
353 315 "Kleinwort Benson"                                  100  27000000
354 316 "Bank of Scotland Plc"                              100  23500000
355 317 "Security Pacific National Bank"                    100  80000000
356 318 "First Interstate Bank of California"               100    600000
357 319 "Security Pacific National Bank"                    100   6000000
358 320 "Merrill Trust"                                     100   4000000
359 320 "Merrill Trust"                                     100   2000000
360 321 "Chemical Bank"                                    28.5  75000000
361 321 "Chemical Bank"                                    28.5 1.000e+08
362 321 "Chemical Bank"                                    28.5  50000000
365 324 "Bankers Trust Co"                                 41.7 6.000e+08
366 325 "Texas Commerce Bank"                               100   3000000
368 326 "Manufacturers Hanover Trust"                       100   2000000
373 329 "Swiss Bank Corp"                                  23.8 4.200e+09
374 330 "First Florida Bank"                                100   5000000
376 332 "Comerica Bank"                                     100   1000000
377 333 "First Interstate Bank of Arizona"                  100   1000000
378 334 "First Interstate Bank of Oregon"                   100    500000
379 335 "Commonwealth Financial Corp"                       100   3000000
380 336 "Philadelphia National Bank"                       28.6  95000000
392 343 "Sanwa Business Credit Corp"                         65  22500000
393 343 "Sanwa Business Credit Corp"                         65   6000000
397 345 "Midland Bank Plc"                                  100   2559415
398 346 "Commerce Bank of Kansas City"                      100   3800000
401 348 "Chemical Bank"                                     100   3500000
402 349 "Bank One Dayton"                                   100   5500000
403 350 "Marine Midland Bank"                             22.32 1.070e+08
405 350 "Marine Midland Bank"                             22.32   5000000
406 351 "First National Bank Minneapolis"                   100  15000000
407 352 "Bank of New England"                              61.9  65625000
410 354 "Mellon Bank (East)"                                100   2500000
412 355 "Southeast Bank"                                    100    800000
413 356 "PNC Bank - Philadelphia"                            40 1.000e+08
414 357 "PNC Bank - Philadelphia"                            55 1.000e+08
417 359 "Bank of the West"                                  100   4000000
418 359 "Bank of the West"                                  100   3000000
419 360 "Security Pacific National Bank"                     30  40000000
420 360 "Security Pacific National Bank"                     30  66300000
421 361 "CIBC [Canadian Imperial Bank of Commerce]"         9.3 3.500e+08
425 364 "Midland Bank Plc"                                  100  15000000
426 365 "First City Texas - Houston"                        100  15000000
428 366 "Heller Financial Inc"                              100   5500000
429 367 "Prudential Insurance Co of America"                100  15000000
431 369 "Connecticut National Bank"                         100   7500000
434 371 "American Security Bank"                            100  50000000
435 372 "Bankers Trust Co"                                   52 1.350e+08
436 372 "Bankers Trust Co"                                   40 1.000e+08
437 372 "Bankers Trust Co"                                 85.7  35000000
438 372 "Bankers Trust Co"                                  100  25000000
439 373 "Rhode Island Hospital Trust National Bank"         100   3523000
440 374 "Bank of America"                                   100   2500000
441 375 "Bank of America"                                   100   5000000
442 376 "Daiwa Bank Trust Co"                               100  25000000
443 377 "American Security Bank"                             79  65000000
end

My goal is to now consolidate the facilities on the packageid (one packageid can contain numerous facilityids). The problem is now that I want to have this one-line consolidation on packageid-level including a calculation: instead of bankallocation and facilityamount I want to calculate (for each packageid) the lender's share in the package.
The calculation for this is:
for each facilityid within the same packageid, multiply (bankallocation/100) [as bankallocation is in %] with the facilityamount. Then sum this up and divide it by the total facilityamt of the packageid [the sum of each facilityamt within the same packageid].
Can this be done in one step or do I have to create new variables for the calculation steps?

My first steps were the follwing, but the problem is that I NEED to preserve the other variables (f.ex. the lender) for the overall packageid which does not work with collapse:

gen lender_share_facility = (bankallocation/100) * facilityamt
collapse (sum) lender_share facilityamt, by(packageid)
gen lender_share_package = lender_share_facility / facilityamt