Summing up values

[Wisura Weerathunga]

I have the following Data set

naics. 1989. 1990. 1991. 1992.

111 xxxxx xxxxx xxxxx xxxxx
113 xxxxx xxxxx xxxxx xxxxx
112 xxxxx xxxxx xxxxx xxxxx
115 xxxxx xxxxx xxxxx xxxxx
114 xxxxx xxxxx xxxxx xxxxx
212 xxxxx xxxxx xxxxx xxxxx
221 xxxxx xxxxx xxxxx xxxxx
213 xxxxx xxxxx xxxxx xxxxx
214 xxxxx xxxxx xxxxx xxxxx


I need to add specific rows together,

generate a new row (111_112) by adding the values for each year for the 111 and 112

generate a new row (113_114_115) by adding values for each year for 113,114 and 115

[Joro Kolev]

There is no rule in your description defining the groups (111 112) and (113 114 115). Create such a rule in your data by defining a new variable (say group) taking the value of 1 in the first group by, second group by 2, etc.

Then

egen total = total(v1989), by(group)

will give you the total for the v1989.

then you can automate this by a loop

for var of varlist v1989. v1990. v1991. v1992. {
egen total`var' = total(`var'), by(group)
}

[Joro Kolev]

Also check the guidelines on how to ask questions here (the FAQs), in particular check out how to show your data by -dataex-.

[Carole J. Wilson]

Trying to force Stata to behave like a spreadsheet is an exercise in frustration. Here is a possible solution to your problem, though it seems like more effort than would likely be necessary if we knew the full scope of what you would ultimately need for analysis or reporting:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 naics float(y1989 y1990 y1991)
"111" 1 209 112
"112" 2 208 114
"113" 3 207 116
"114" 5 205 120
"115" 4 206 118
"212" 6 204 122
"213" 8 202 126
"214" 9 201 128
"221" 7 203 124
end

local newN=`=_N'+1
set obs `newN'
replace naics="111_112" in `=_N'

local newN=`=_N'+1
set obs `newN'
replace naics="113_114_115" in `=_N'

reshape long y, i(naics) j(year)
bysort year: egen x=total(y) if inlist(naics, "111", "112")
bysort year: egen z=max(x)
replace y=z if naics=="111_112"
drop x z
bysort year: egen x=total(y) if inlist(naics, "113", "114", "115")
bysort year: egen z=max(x)
replace y=z if naics=="113_114_115"
drop x z
reshape wide

[Wisura Weerathunga]

Thanks Jero, this worked.

Yes I will read the FAQs. Thanks for the information.

[David Benson]

Trying to force Stata to behave like a spreadsheet is an exercise in frustration.

This might actually be something to do in Excel. You would need to create a variable that indicated which groups to keep together, and then use Data > Subtotals.

You might also be able to accomplish what you want using collapse (as long as you had created a variable, like group) that listed which NAICS you wanted to combine.) (Be sure to save your data beforehand, collapse deletes data to create a new dataset with the group totals).

See also, "Addition by SIC", here

dataex naics group v1989 v1990 v1991  // data shared using -dataex-. To install: ssc install dataex
clear
input int naics byte group int(v1989 v1990 v1991)
111 1 137 139 196
112 1 215 207 116
113 2 195 245 123
114 2 169 196 178
115 2 222 191 124
212 3 128 141 144
213 3 192 203 176
214 3 191 182 183
221 4 113 197 123
311 5 184 213 187
312 5 175 200 187
313 5 131 223 142
end
------------------ copy up to and including the previous line ------------------

list, sepby(group) noobs

  +---------------------------------------+
  | naics   group   v1989   v1990   v1991 |
  |---------------------------------------|
  |   111       1     137     139     196 |
  |   112       1     215     207     116 |
  |---------------------------------------|
  |   113       2     195     245     123 |
  |   114       2     169     196     178 |
  |   115       2     222     191     124 |
  |---------------------------------------|
  |   212       3     128     141     144 |
  |   213       3     192     203     176 |
  |   214       3     191     182     183 |
  |---------------------------------------|
  |   221       4     113     197     123 |
  |---------------------------------------|
  |   311       5     184     213     187 |
  |   312       5     175     200     187 |
  |   313       5     131     223     142 |
  +---------------------------------------+

. table group, c(n naics sum v1989 sum v1990 sum v1991) row col

----------------------------------------------------------
    group |   N(naics)  sum(v1989)  sum(v1990)  sum(v1991)
----------+-----------------------------------------------
        1 |          2         352         346         312
        2 |          3         586         632         425
        3 |          3         511         526         503
        4 |          1         113         197         123
        5 |          3         490         636         516
          | 
    Total |         12        2052        2337        1879
----------------------------------------------------------


preserve
collapse (sum) v1989 v1990 v1991, by(group)

. list, noobs

  +-------------------------------+
  | group   v1989   v1990   v1991 |
  |-------------------------------|
  |     1     352     346     312 |
  |     2     586     632     425 |
  |     3     511     526     503 |
  |     4     113     197     123 |
  |     5     490     636     516 |
  +-------------------------------+

restore

[Wisura Weerathunga]

Thanks David for the information, I created a grouped variable as you have mentioned, and then used the collapse command. This worked well, Thanks again!