Help with counting observations by year as a percentage

[Carlos Ingeberg]

Hello statalist I want to make a descriptive analysis using my dataset. I have 7 independent variables and 1 independent variable called “politisering”. I want to count the total number of observations per year for each independent variable = 1 as a percentage if that variable has the value 1 on my dependent variable “politisering”.

Example: Say for the year 2000 I have a total 40 observations for my independent variable “kontorchef” = 1. Of those 40 observations, 10 have the value 1 on “politisering”. I then want to calculate the percentage of that and in a new variable.

I want to do this for each year with each variable, so that there is only 1 observation per variable per year. Can anyone help me out with what command I should use?
Any advice is much appreciated!

I have the following dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input int år byte(fuldmægtig kontorchef afdelingschef departementschef koordinerende velfærd politisering)
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 0 0 0 0 0 0 0
1982 0 1 0 0 0 0 0
1982 0 0 1 0 0 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 1 0 0 0 1 0 0
1982 0 1 0 0 1 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 1 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 0 0
1982 1 0 0 0 0 1 0
1983 1 0 0 0 0 0 0
1983 1 0 0 0 0 0 0
1983 1 0 0 0 0 0 0
1983 1 0 0 0 0 0 0
1983 1 0 0 0 0 1 0
1983 1 0 0 0 0 0 0
1983 1 0 0 0 0 1 0
1983 1 0 0 0 0 1 0
1983 1 0 0 0 0 1 0
1983 1 0 0 0 0 1 0
1983 1 0 0 0 0 0 0
1983 1 0 0 0 0 0 0
end

[Carlo Lazzaro]

Carlos:
why not considering -collapse- if you want one observation per year?

[Nick Cox]

The percent of 1s is just the mean over a 0, 1 variable multiplied by 100. If I can follow this correctly, you want something like

egen kontorchef_wanted = mean(100 * politsering), by(kontorchef year)

or possibly something like

egen kontorchef_wanted2 = mean(cond(kontorchef == 1, 100 * politsering, .)), by(year)

[Carlos Ingeberg]

Thanks for a fast reply! Carlo, when i use the collapse command it count the total number observations per year for each variable, and not as a percentage as i wanted. Im note sure if there is some way to collapse and get the number as percentage as described.
Nick, what you have suggest in your second command seems to work nicely. Maybe i can use the collapse command after running your second code to get one observation per year?

[Carlo Lazzaro]

Carlos:
my take was something like:

. collapse (count) fuldmægtig kontorchef afdelingschef departementschef koordinerende velfærd politisering, by(år)


. foreach var of varlist fuldmægtig- politisering {
  2. replace `var'=`var'/100
  3.  }

. list

     +----------------------------------------------------------------------------------+
     |   år   fuldmæ~g   kontor~f   afdeli~f   depart~f   koordi~e   velfærd   politi~g |
     |----------------------------------------------------------------------------------|
  1. | 1982        .88        .88        .88        .88        .88       .88        .88 |
  2. | 1983        .12        .12        .12        .12        .12       .12        .12 |
     +----------------------------------------------------------------------------------+

.

[Nick Cox]

If my second command was what you want, collapse can't help you here as you want to average over different groups for each independent variable.