Addition by SIC

[Wisura Weerathunga]

Hi, Can you please help with the below.

I need to add values by SIC code in the below example. (similar to using a pivot table in excel or filtering).

SIC Value of imports
123 10000
456 12344
789 122312
987 234233
123 12344
123 21123
789 344234
987 142344
456 344232


I need to get SIC wise total import values. (total value of imports of each SIC)
eg(total value of imports for the SIC "123")

Thank you.

[David Benson]

Wisura, welcome to Statalist!

* Example generated by -dataex-. To install: ssc install dataex
clear
input int sic long value_of_imports
123  10000
123  12344
123  21123
456  12344
456 344232
789 122312
789 344234
987 142344
987 234233
end

sort sic val
egen sic_total = total( value_of_imports), by(sic)
// Could also be written as  "bysort sic: egen s_total3 = total( value_of_imports)"
format  value_of_imports sic_total %10.0gc
by sic: gen n = _n
by sic: gen count = _N // just creating some count variables
list, sepby(sic) noobs abbrev(16)


  +------------------------------------------------+
  | sic   value_of_imports   sic_total   n   count |
  |------------------------------------------------|
  | 123             10,000      43,467   1       3 |
  | 123             12,344      43,467   2       3 |
  | 123             21,123      43,467   3       3 |
  |------------------------------------------------|
  | 456             12,344     356,576   1       2 |
  | 456            344,232     356,576   2       2 |
  |------------------------------------------------|
  | 789            122,312     466,546   1       2 |
  | 789            344,234     466,546   2       2 |
  |------------------------------------------------|
  | 987            142,344     376,577   1       2 |
  | 987            234,233     376,577   2       2 |
  +------------------------------------------------+

If you want to collapse it down to 1 line per SIC code (that has count of observations and adds the total):

preserve
collapse (count) count_imports = value_of_imports (sum) total_value = value_of_imports, by(sic)

. list, noobs abbrev(16)

  +-----------------------------------+
  | sic   count_imports   total_value |
  |-----------------------------------|
  | 123               3        43,467 |
  | 456               2       356,576 |
  | 789               2       466,546 |
  | 987               2       376,577 |
  +-----------------------------------+


*** If you wanted more summary statistics
collapse (count) count_imports = value_of_imports (sum) total_value = value_of_imports (mean) avg_imports = value_of_imports (min) min_imports = value_of_imports (max) max_imports = value_of_imports , by(sic)
format total_value avg_i min_imports max_imports %10.0gc
list, noobs abbrev(16)

  +-----------------------------------------------------------------------------+
  | sic   count_imports   total_value   avg_imports   min_imports   max_imports |
  |-----------------------------------------------------------------------------|
  | 123               3        43,467        14,489        10,000        21,123 |
  | 456               2       356,576       178,288        12,344       344,232 |
  | 789               2       466,546       233,273       122,312       344,234 |
  | 987               2       376,577     188,288.5       142,344       234,233 |
  +-----------------------------------------------------------------------------+

[Wisura Weerathunga]

Hi David,
Thanks a lot for the detailed explanation, it works perfectly..