Extract New Variable from Frequency Table by Category

[Charles Ehmat]

Hey everyone,

I am using Stata 16 on Mac.

I am trying to create a new variable from the percentages I obtained from a frequency table.

The data contains individuals that live in different villages and took part in an election or abstained. To measure the voter turnout by village, I created a frequency table including the percentages using the following command:

tab village vote_brgy, row

This is a part of the resulting frequency table:

+----------------+
| Key            |
|----------------|
|   frequency    |
| row percentage |
+----------------+

               |     Voted at
               |       elections
       village |         0          1 |     Total
---------------+----------------------+----------
   Balac-Balac |         2         25 |        27 
               |      7.41      92.59 |    100.00 
---------------+----------------------+----------
         Baras |         2         25 |        27 
               |      7.41      92.59 |    100.00 
---------------+----------------------+----------
   Batonan-Sur |         0         27 |        27 
               |      0.00     100.00 |    100.00 
---------------+----------------------+----------
        Bucaya |         0         27 |        27 
               |      0.00     100.00 |    100.00

What I want to do now is create a new variable that contains the voter turnout (i.e. 92% vor Balac-Balac). This percentage should be displayed for every participant that lives in that village. It is important to mention that there are a lot of missing variables for this variable. For example there were 56 participants from Balac-Balac, but only 27 answered the question regarding their voting behaviour in the last election. I thought to do this by hand but there are many different villages so I hope to avoid that.

I hope I clearly stated my problem.

Thank you in advance for your time and best regards
Charles Ehmat

[Andrew Musau]

The mean of an indicator is a proportion. Multiply by 100 to get percentage.

bys village: egen wanted = mean(vote_brgy*100)

See

help egen

[Charles Ehmat]

Thank you so much. That is an angle I had not thought of!

But is there a way to extract the percentages from the frequency table anyway? Especially for cases that don't have binary indicator variables ranges? For example there are other variables in the dataset with values ranging from 1 to 5 etc

[Andrew Musau]

Almost certainly, you will not want to manually copy frequencies. You can always use devices such as #2.

sysuse auto, clear
tab foreign rep78, row

Res.:

 tab foreign rep78, row

+----------------+
| Key            |
|----------------|
|   frequency    |
| row percentage |
+----------------+

           |                   Repair Record 1978
  Car type |         1          2          3          4          5 |     Total
-----------+-------------------------------------------------------+----------
  Domestic |         2          8         27          9          2 |        48
           |      4.17      16.67      56.25      18.75       4.17 |    100.00
-----------+-------------------------------------------------------+----------
   Foreign |         0          0          3          9          9 |        21
           |      0.00       0.00      14.29      42.86      42.86 |    100.00
-----------+-------------------------------------------------------+----------
     Total |         2          8         30         18         11 |        69
           |      2.90      11.59      43.48      26.09      15.94 |    100.00

Using egen:

sysuse auto, clear
bys foreign: egen total= count(rep78)
bys foreign rep78: egen wanted= count(rep78)
replace wanted= (wanted/total)*100

Res.:

. bys foreign rep78: gen first=_n==1

. list foreign rep78 wanted if first, sepby(foreign)

     +-----------------------------+
     |  foreign   rep78     wanted |
     |-----------------------------|
  1. | Domestic       1   4.166667 |
  3. | Domestic       2   16.66667 |
 11. | Domestic       3      56.25 |
 38. | Domestic       4      18.75 |
 47. | Domestic       5   4.166667 |
 49. | Domestic       .          0 |
     |-----------------------------|
 53. |  Foreign       3   14.28571 |
 56. |  Foreign       4   42.85714 |
 65. |  Foreign       5   42.85714 |
 74. |  Foreign       .          0 |
     +-----------------------------+

[Charles Ehmat]

Thank you very much! That helped a lot. Appreciate it!