Frequency Table with 2 variables

Roy te Riele

Join Date: May 2021

Posts: 11
#1

Frequency Table with 2 variables

14 May 2021, 10:48

Hi Guys,

I'm currently struggling with making a frequency table which consists of two variables. I would like to sort the frequency table based on the industry (sic2 code) and per fiscal year (year). The frequency table which I have in mind and which I'm trying to remake is the following:

Do you guys know the command with which I can generate this frequency table. Next to that, I would then also like to export this frequency table into word. Do you guys maybe also know the command for this export?

Thank you very much and I would like to hear from you

Kind regards,
Roy

Last edited by Roy te Riele; 14 May 2021, 11:42.
Tags: None
Andrew Musau

Join Date: Oct 2014

Posts: 10118
#2

14 May 2021, 12:47

Present a data example using dataex. Refer to FAQ Advice #12 for more details.
Comment
Roy te Riele

Join Date: May 2021

Posts: 11
#3

14 May 2021, 13:35

Hi Andrew,

Hereby a data example by using dataex:

year conm sic2 EQ
2012 "AAON INC" "35" .1929535
2013 "AAON INC" "35" .18748043
2014 "AAON INC" "35" .1982954
2015 "AAON INC" "35" .030175705
2016 "AAON INC" "35" .6359696
2017 "AAON INC" "35" -.150964
2018 "AAON INC" "35" .13465497
2012 "AAR CORP" "50" -.015378376
2013 "AAR CORP" "50" .19554286
2014 "AAR CORP" "50" -.05181314
2015 "AAR CORP" "50" .5184011
2016 "AAR CORP" "50" .06149638
2017 "AAR CORP" "50" .13521312
2018 "AAR CORP" "50" .22805415
2012 "ABM INDUSTRIES INC" "73" .1006308
2013 "ABM INDUSTRIES INC" "73" .20459394
2014 "ABM INDUSTRIES INC" "73" .19703226
2015 "ABM INDUSTRIES INC" "73" .0853871
2016 "ABM INDUSTRIES INC" "73" .2050887
2017 "ABM INDUSTRIES INC" "73" .3859319
2018 "ABM INDUSTRIES INC" "73" .14618774
2013 "ADT CORP" "73" .25455084
2014 "ADT CORP" "73" .2375647
2015 "ADT CORP" "73" .27108708
2012 "AGCO CORP" "35" .08643262
2013 "AGCO CORP" "35" .1542923
2014 "AGCO CORP" "35" .3583142
2015 "AGCO CORP" "35" .06786635
2016 "AGCO CORP" "35" .1302592
2017 "AGCO CORP" "35" .05225388
2018 "AGCO CORP" "35" .13623206
2012 "AES CORP (THE)" "49" .05090952
2013 "AES CORP (THE)" "49" -.01513626
2014 "AES CORP (THE)" "49" .06661307
2015 "AES CORP (THE)" "49" -.010163924
2016 "AES CORP (THE)" "49" .04543915
2017 "AES CORP (THE)" "49" .0037344296
2018 "AES CORP (THE)" "49" -.018573513
2012 "AK STEEL HOLDING CORP" "33" -.1226684
2013 "AK STEEL HOLDING CORP" "33" .04980338
2014 "AK STEEL HOLDING CORP" "33" -.16087344
2015 "AK STEEL HOLDING CORP" "33" .032117397
2016 "AK STEEL HOLDING CORP" "33" -.0011114036
2017 "AK STEEL HOLDING CORP" "33" .03938179
2018 "AK STEEL HOLDING CORP" "33" .009947551
2016 "AMAG PHARMACEUTICALS INC" "28" .0553001
2017 "AMAG PHARMACEUTICALS INC" "28" .25104892
2018 "AMAG PHARMACEUTICALS INC" "28" .06559715
2012 "AMC NETWORKS INC" "48" -.1533962
2013 "AMC NETWORKS INC" "48" .2074865
2014 "AMC NETWORKS INC" "48" -.34809485
2015 "AMC NETWORKS INC" "48" .09752102
2016 "AMC NETWORKS INC" "48" -.04939316
2017 "AMC NETWORKS INC" "48" .1841279
2018 "AMC NETWORKS INC" "48" .3633247
2012 "AMN HEALTHCARE SERVICES INC" "73" .04923699
2013 "AMN HEALTHCARE SERVICES INC" "73" .22313583
2014 "AMN HEALTHCARE SERVICES INC" "73" .2338148
2015 "AMN HEALTHCARE SERVICES INC" "73" .5670025
2016 "AMN HEALTHCARE SERVICES INC" "73" .24356993
2017 "AMN HEALTHCARE SERVICES INC" "73" .3744881
2018 "AMN HEALTHCARE SERVICES INC" "73" .11924363
2015 "ANI PHARMACEUTICALS INC" "28" .36267805
2016 "ANI PHARMACEUTICALS INC" "28" .11611132
2017 "ANI PHARMACEUTICALS INC" "28" .28646368
2018 "ANI PHARMACEUTICALS INC" "28" .16557354
2013 "AOL INC" "73" .3109156
2014 "AOL INC" "73" .2469747
2017 "ASGN INC" "73" .3470015
2018 "ASGN INC" "73" .1672229
2012 "AT&T INC" "48" -.018591814
2013 "AT&T INC" "48" .11049849
2014 "AT&T INC" "48" .20008707
2015 "AT&T INC" "48" -.034160264
2016 "AT&T INC" "48" .029135825
2017 "AT&T INC" "48" .020985443
2018 "AT&T INC" "48" .17057034
2016 "ATN INTERNATIONAL INC" "48" .0277395
2017 "ATN INTERNATIONAL INC" "48" -.00688722
2018 "ATN INTERNATIONAL INC" "48" .09375823
2012 "AZZ INC" "36" .0491948
2013 "AZZ INC" "36" .17655283
2014 "AZZ INC" "36" .09885216
2015 "AZZ INC" "36" -.015666032
2016 "AZZ INC" "36" .1100118
2017 "AZZ INC" "36" .06719983
2018 "AZZ INC" "36" .14120881
2012 "ABAXIS INC" "38" .11204783
2013 "ABAXIS INC" "38" .09547625
2014 "ABAXIS INC" "38" .3259621
2015 "ABAXIS INC" "38" .25728834
2016 "ABAXIS INC" "38" .28315055
2017 "ABAXIS INC" "38" .18829893
2012 "ABBOTT LABORATORIES" "28" .3053475
2013 "ABBOTT LABORATORIES" "28" .4991478
2014 "ABBOTT LABORATORIES" "28" .4862678
2015 "ABBOTT LABORATORIES" "28" .4170674
2016 "ABBOTT LABORATORIES" "28" .30992135
2017 "ABBOTT LABORATORIES" "28" .3372451
2018 "ABBOTT LABORATORIES" "28" .3054671

I hope that I've informed you enough with this information for creating a command for the frequency table. The sample consist of company observations over the years 2010-2019.

Kind regards,
Roy
Comment

Jeff Pitblado (StataCorp)

StataCorp Employee

Join Date: Mar 2014
Posts: 687

14 May 2021, 13:53

The table of interest can be composed by combining collections from 2 calls to the new table command in Stata 17.

The following example is based on data simulated using the information in the original post.

Code:

* simulate example data
set seed 17
input sic
1
10
12
13
14
15
16
17
20
end
label define sic_desc ///
    1  "01  Agricultural - Crops" ///
    10 "10  Metal Mining" ///
    12 "12  Coal Mining" ///
    13 "13  Oil and Gas Extraction" ///
    14 "14  Mining and Quarrying" ///
    15 "15  Building Construction" ///
    16 "16  Heavy Construction" ///
    17 "17  Construction - Special" ///
    20 "20  Food and Kindred"
label values sic sic_desc
expand 9
bysort sic: gen year = 2006 + _n
gen x = runiformint(1,50)
expand x
drop x

* compute frequency statistics
table (sic) (year), name(sic_year_freq) stat(freq) totals(sic)

* compute percentage statistics
table (sic), name(sic_perc) stat(percent) nototals

* use custom label
collect label levels result percent "Percentage", modify
collect preview

* polish look of percent values
collect style cell result[percent], sformat("%s%%")
collect preview

* combine collections
collect combine both = sic_year_freq sic_perc
collect layout (sic) (result[frequency]#year result[percent])

* polish headers
collect style header sic year, title(hide)
collect style header result[frequency], level(hide)
collect preview

* remove vertical borders
collect style cell border_block, border(right, pattern(nil))
collect preview

* export to docx
collect export sic.docx

Here is a log from running the above in Stata 17.

Code:

. * simulate example data
. set seed 17

. input sic

           sic
  1. 1
  2. 10
  3. 12
  4. 13
  5. 14
  6. 15
  7. 16
  8. 17
  9. 20
 10. end

. label define sic_desc ///
>         1  "01  Agricultural - Crops" ///
>         10 "10  Metal Mining" ///
>         12 "12  Coal Mining" ///
>         13 "13  Oil and Gas Extraction" ///
>         14 "14  Mining and Quarrying" ///
>         15 "15  Building Construction" ///
>         16 "16  Heavy Construction" ///
>         17 "17  Construction - Special" ///
>         20 "20  Food and Kindred"

. label values sic sic_desc

. expand 9
(72 observations created)

. bysort sic: gen year = 2006 + _n

. gen x = runiformint(1,50)

. expand x
(2,119 observations created)

. drop x

. 
. * compute frequency statistics
. table (sic) (year), name(sic_year_freq) stat(freq) totals(sic)

----------------------------------------------------------------------------------------------------
                             |                                  year                                
                             |  2007   2008   2009   2010   2011   2012   2013   2014   2015   Total
-----------------------------+----------------------------------------------------------------------
sic                          |                                                                      
  01  Agricultural - Crops   |    45      2     45     43     26     47      3     16     21     248
  10  Metal Mining           |    34     22     25     10     23     48     28     27      1     218
  12  Coal Mining            |    34     34     30     32     41     29      1     25     28     254
  13  Oil and Gas Extraction |    43     48     30     23     21     30     34     30     34     293
  14  Mining and Quarrying   |    47     18     32     29      9     27      7     17     18     204
  15  Building Construction  |    37     38     50     27     24     24     50     34     17     301
  16  Heavy Construction     |    28      2     25     45     33     50     37     43     37     300
  17  Construction - Special |    26      1      8     19     24     10     34     42     26     190
  20  Food and Kindred       |    21     18     48     12     18      7     46      1     21     192
----------------------------------------------------------------------------------------------------

. 
. * compute percentage statistics
. table (sic), name(sic_perc) stat(percent) nototals

---------------------------------------
                             |  Percent
-----------------------------+---------
sic                          |         
  01  Agricultural - Crops   |    11.27
  10  Metal Mining           |     9.91
  12  Coal Mining            |    11.55
  13  Oil and Gas Extraction |    13.32
  14  Mining and Quarrying   |     9.27
  15  Building Construction  |    13.68
  16  Heavy Construction     |    13.64
  17  Construction - Special |     8.64
  20  Food and Kindred       |     8.73
---------------------------------------

. 
. * use custom label
. collect label levels result percent "Percentage", modify

. collect preview

------------------------------------------
                             |  Percentage
-----------------------------+------------
sic                          |            
  01  Agricultural - Crops   |       11.27
  10  Metal Mining           |        9.91
  12  Coal Mining            |       11.55
  13  Oil and Gas Extraction |       13.32
  14  Mining and Quarrying   |        9.27
  15  Building Construction  |       13.68
  16  Heavy Construction     |       13.64
  17  Construction - Special |        8.64
  20  Food and Kindred       |        8.73
------------------------------------------

. 
. * polish look of percent values
. collect style cell result[percent], sformat("%s%%")

. collect preview

------------------------------------------
                             |  Percentage
-----------------------------+------------
sic                          |            
  01  Agricultural - Crops   |      11.27%
  10  Metal Mining           |       9.91%
  12  Coal Mining            |      11.55%
  13  Oil and Gas Extraction |      13.32%
  14  Mining and Quarrying   |       9.27%
  15  Building Construction  |      13.68%
  16  Heavy Construction     |      13.64%
  17  Construction - Special |       8.64%
  20  Food and Kindred       |       8.73%
------------------------------------------

. 
. * combine collections
. collect combine both = sic_year_freq sic_perc
(current collection is both)

. collect layout (sic) (result[frequency]#year result[percent])

Collection: both
      Rows: sic
   Columns: result[frequency]#year result[percent]
   Table 1: 10 x 11

-----------------------------------------------------------------------------------------------------------------
                             |                                Frequency                                Percentage
                             |                                  year                                             
                             |  2007   2008   2009   2010   2011   2012   2013   2014   2015   Total             
-----------------------------+-----------------------------------------------------------------------------------
sic                          |                                                                                   
  01  Agricultural - Crops   |    45      2     45     43     26     47      3     16     21     248       11.27%
  10  Metal Mining           |    34     22     25     10     23     48     28     27      1     218        9.91%
  12  Coal Mining            |    34     34     30     32     41     29      1     25     28     254       11.55%
  13  Oil and Gas Extraction |    43     48     30     23     21     30     34     30     34     293       13.32%
  14  Mining and Quarrying   |    47     18     32     29      9     27      7     17     18     204        9.27%
  15  Building Construction  |    37     38     50     27     24     24     50     34     17     301       13.68%
  16  Heavy Construction     |    28      2     25     45     33     50     37     43     37     300       13.64%
  17  Construction - Special |    26      1      8     19     24     10     34     42     26     190        8.64%
  20  Food and Kindred       |    21     18     48     12     18      7     46      1     21     192        8.73%
-----------------------------------------------------------------------------------------------------------------

. 
. * polish headers
. collect style header sic year, title(hide)

. collect style header result[frequency], level(hide)

. collect preview

---------------------------------------------------------------------------------------------------------------
                           |  2007   2008   2009   2010   2011   2012   2013   2014   2015   Total   Percentage
---------------------------+-----------------------------------------------------------------------------------
01  Agricultural - Crops   |    45      2     45     43     26     47      3     16     21     248       11.27%
10  Metal Mining           |    34     22     25     10     23     48     28     27      1     218        9.91%
12  Coal Mining            |    34     34     30     32     41     29      1     25     28     254       11.55%
13  Oil and Gas Extraction |    43     48     30     23     21     30     34     30     34     293       13.32%
14  Mining and Quarrying   |    47     18     32     29      9     27      7     17     18     204        9.27%
15  Building Construction  |    37     38     50     27     24     24     50     34     17     301       13.68%
16  Heavy Construction     |    28      2     25     45     33     50     37     43     37     300       13.64%
17  Construction - Special |    26      1      8     19     24     10     34     42     26     190        8.64%
20  Food and Kindred       |    21     18     48     12     18      7     46      1     21     192        8.73%
---------------------------------------------------------------------------------------------------------------

. 
. * remove vertical borders
. collect style cell border_block, border(right, pattern(nil))

. collect preview

-------------------------------------------------------------------------------------------------------------
                            2007   2008   2009   2010   2011   2012   2013   2014   2015   Total   Percentage
-------------------------------------------------------------------------------------------------------------
01  Agricultural - Crops      45      2     45     43     26     47      3     16     21     248       11.27%
10  Metal Mining              34     22     25     10     23     48     28     27      1     218        9.91%
12  Coal Mining               34     34     30     32     41     29      1     25     28     254       11.55%
13  Oil and Gas Extraction    43     48     30     23     21     30     34     30     34     293       13.32%
14  Mining and Quarrying      47     18     32     29      9     27      7     17     18     204        9.27%
15  Building Construction     37     38     50     27     24     24     50     34     17     301       13.68%
16  Heavy Construction        28      2     25     45     33     50     37     43     37     300       13.64%
17  Construction - Special    26      1      8     19     24     10     34     42     26     190        8.64%
20  Food and Kindred          21     18     48     12     18      7     46      1     21     192        8.73%
-------------------------------------------------------------------------------------------------------------

. 
. * export to docx
. collect export sic.docx
(collection both exported to file sic.docx)

Comment

Andrew Musau

Join Date: Oct 2014
Posts: 10118

14 May 2021, 14:46

You can also install estout from SSC.

Code:

ssc install estout, replace

For your example with exactly 100 observations, the percentages coincide with the totals.

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float year str20 conm str4 sic2 double EQ
2012 "AAON INC"             "35"     .1929535
2013 "AAON INC"             "35"    .18748043
2014 "AAON INC"             "35"     .1982954
2015 "AAON INC"             "35"   .030175705
2016 "AAON INC"             "35"     .6359696
2017 "AAON INC"             "35"     -.150964
2018 "AAON INC"             "35"    .13465497
2012 "AAR CORP"             "50"  -.015378376
2013 "AAR CORP"             "50"    .19554286
2014 "AAR CORP"             "50"   -.05181314
2015 "AAR CORP"             "50"     .5184011
2016 "AAR CORP"             "50"    .06149638
2017 "AAR CORP"             "50"    .13521312
2018 "AAR CORP"             "50"    .22805415
2012 "ABM INDUSTRIES INC"   "73"     .1006308
2013 "ABM INDUSTRIES INC"   "73"    .20459394
2014 "ABM INDUSTRIES INC"   "73"    .19703226
2015 "ABM INDUSTRIES INC"   "73"     .0853871
2016 "ABM INDUSTRIES INC"   "73"     .2050887
2017 "ABM INDUSTRIES INC"   "73"     .3859319
2018 "ABM INDUSTRIES INC"   "73"    .14618774
2013 "ADT CORP"             "73"    .25455084
2014 "ADT CORP"             "73"     .2375647
2015 "ADT CORP"             "73"    .27108708
2012 "AGCO CORP"            "35"    .08643262
2013 "AGCO CORP"            "35"     .1542923
2014 "AGCO CORP"            "35"     .3583142
2015 "AGCO CORP"            "35"    .06786635
2016 "AGCO CORP"            "35"     .1302592
2017 "AGCO CORP"            "35"    .05225388
2018 "AGCO CORP"            "35"    .13623206
2012 "AES CORP (THE)"       "49"    .05090952
2013 "AES CORP (THE)"       "49"   -.01513626
2014 "AES CORP (THE)"       "49"    .06661307
2015 "AES CORP (THE)"       "49"  -.010163924
2016 "AES CORP (THE)"       "49"    .04543915
2017 "AES CORP (THE)"       "49"  .0037344296
2018 "AES CORP (THE)"       "49"  -.018573513
2012 "AK STEEL HOLDING COR" "33"    -.1226684
2013 "AK STEEL HOLDING COR" "33"    .04980338
2014 "AK STEEL HOLDING COR" "33"   -.16087344
2015 "AK STEEL HOLDING COR" "33"   .032117397
2016 "AK STEEL HOLDING COR" "33" -.0011114036
2017 "AK STEEL HOLDING COR" "33"    .03938179
2018 "AK STEEL HOLDING COR" "33"   .009947551
2016 "AMAG PHARMACEUTICALS" "28"     .0553001
2017 "AMAG PHARMACEUTICALS" "28"    .25104892
2018 "AMAG PHARMACEUTICALS" "28"    .06559715
2012 "AMC NETWORKS INC"     "48"    -.1533962
2013 "AMC NETWORKS INC"     "48"     .2074865
2014 "AMC NETWORKS INC"     "48"   -.34809485
2015 "AMC NETWORKS INC"     "48"    .09752102
2016 "AMC NETWORKS INC"     "48"   -.04939316
2017 "AMC NETWORKS INC"     "48"     .1841279
2018 "AMC NETWORKS INC"     "48"     .3633247
2012 "AMN HEALTHCARE SERVI" "73"    .04923699
2013 "AMN HEALTHCARE SERVI" "73"    .22313583
2014 "AMN HEALTHCARE SERVI" "73"     .2338148
2015 "AMN HEALTHCARE SERVI" "73"     .5670025
2016 "AMN HEALTHCARE SERVI" "73"    .24356993
2017 "AMN HEALTHCARE SERVI" "73"     .3744881
2018 "AMN HEALTHCARE SERVI" "73"    .11924363
2015 "ANI PHARMACEUTICALS " "28"    .36267805
2016 "ANI PHARMACEUTICALS " "28"    .11611132
2017 "ANI PHARMACEUTICALS " "28"    .28646368
2018 "ANI PHARMACEUTICALS " "28"    .16557354
2013 "AOL INC"              "73"     .3109156
2014 "AOL INC"              "73"     .2469747
2017 "ASGN INC"             "73"     .3470015
2018 "ASGN INC"             "73"     .1672229
2012 "AT&T INC"             "48"  -.018591814
2013 "AT&T INC"             "48"    .11049849
2014 "AT&T INC"             "48"    .20008707
2015 "AT&T INC"             "48"  -.034160264
2016 "AT&T INC"             "48"   .029135825
2017 "AT&T INC"             "48"   .020985443
2018 "AT&T INC"             "48"    .17057034
2016 "ATN INTERNATIONAL IN" "48"     .0277395
2017 "ATN INTERNATIONAL IN" "48"   -.00688722
2018 "ATN INTERNATIONAL IN" "48"    .09375823
2012 "AZZ INC"              "36"     .0491948
2013 "AZZ INC"              "36"    .17655283
2014 "AZZ INC"              "36"    .09885216
2015 "AZZ INC"              "36"  -.015666032
2016 "AZZ INC"              "36"     .1100118
2017 "AZZ INC"              "36"    .06719983
2018 "AZZ INC"              "36"    .14120881
2012 "ABAXIS INC"           "38"    .11204783
2013 "ABAXIS INC"           "38"    .09547625
2014 "ABAXIS INC"           "38"     .3259621
2015 "ABAXIS INC"           "38"    .25728834
2016 "ABAXIS INC"           "38"    .28315055
2017 "ABAXIS INC"           "38"    .18829893
2012 "ABBOTT LABORATORIES"  "28"     .3053475
2013 "ABBOTT LABORATORIES"  "28"     .4991478
2014 "ABBOTT LABORATORIES"  "28"     .4862678
2015 "ABBOTT LABORATORIES"  "28"     .4170674
2016 "ABBOTT LABORATORIES"  "28"    .30992135
2017 "ABBOTT LABORATORIES"  "28"     .3372451
2018 "ABBOTT LABORATORIES"  "28"     .3054671
end

preserve
contract conm sic2 year, freq(fq)
reshape wide fq, i(conm sic2) j(year)
egen Total= rowtotal(fq*)
egen Percentage= total(Total)
replace Percentage = (Total/Percentage)*100
foreach var of varlist fq*{
    replace `var'=0 if missing(`var')
}
destring sic2, replace
qui estpost tabstat sic2 fq* Total Percentage, by(conm)
esttab using myfile.rtf, ///
cells("sic2 fq2012 fq2013 fq2014 fq2015 fq2016 fq2017 fq2018 Total Percentage") ///
nomtitle nonumber varlabels(`e(labels)') modelwidth(4) varwidth(29) ///
drop(Total) replace noobs substitute("fq" "" "Total" "Tot." "Percentage" "%")

Res.:

Click image for larger version

Name: Untitled.png
Views: 1
Size: 58.3 KB
ID: 1609719

Last edited by Andrew Musau; 14 May 2021, 14:50.

Comment

Roy te Riele

Join Date: May 2021

Posts: 11
#6

15 May 2021, 04:13

Hi Andrew and Jeff,

Thanks for your fast responses on my question. However, I think I should specify my wish in more detail. I would like to create a frequency table for the following sic categories. The breakdown per year can be disregarded in my opinion. So a table with the total observations per SIC category and a column with the percentage of the total observations will be good enough for me. As already stated above, there is a sic2 variable in my dataset which can be used to make this frequency table.

Mining (SIC 10-14)
Construction (SIC 15-17)
Manufacturing (SIC 20-39)
Transportation, Communication & Public Utilitites (SIC 40-49)
Wholesale Trade (SIC 50-51)
Retail Trade (SIC 52-59)
Finance, Insurance, Real Estate (SIC 60-67)
Services (SIC 70-89)
Nonclassifiable Establishments (SIC 99)

Thank you very much for your help and I would like to hear from you.

Kind regards,
Roy
Comment
Andrew Musau

Join Date: Oct 2014

Posts: 10118
#7

15 May 2021, 04:45

At some point, you should be able to study code and make any needed changes. Both #4 and #5 provide you with an outline on how to proceed. See

Code:

help generate

Code:

help inrange()

Code:

help replace

Code:

help if

for how to create a variable with the 2-digit SIC descriptions. Then

contract newvar year, freq(fq)

where "newvar" is the just created variable. The rest of the code does not change (except that you replace "conm" with the name of your new variable and remove "sic2").

Last edited by Andrew Musau; 15 May 2021, 05:08.
Comment

Announcement