Summing a variable based on the values of other categorical variables

[David Castro Pena]

Hi there,

I am learning STATA and currently having the following situation: I want to sum a binary variable that counts the number of council members. I want to sum council members that belong to the same party and the same municipality. The variables municipality and party are string variables. To illustrate my case, this an example of my dataset:

municipality	Party	is_council_member	num_council_party_municip
BRA	CONS	1	2
COL	LIB	0	1
BRA	CONS	1	2
COL	LIB	1	1
BRA	LIB	1	1
COL	CONS	1	1

Using my example, I want to sum the variable "is_council_member" if they belong to the same "MUNICIPALITY" and the same "PARTY". In one municipality, there might be several parties. Therefore, the sum for rows with the same party and municipality should have the same number. The expected result of this conditional sum is the variable "num_council_party_municip"

I tried (unsuccesfully) to use a loop to compare each pair of observations n and n-1 and determine if their parties and municipalities were the same for summing the variable of interest:

gen num_council_party_mun =.

    foreach v of var municipality {
        replace num_council_party_mun = sum(is_council_member)  if municipality[_n]==municipality[_n-1]| party1[_n]==party1[_n-1]
    }

I would really appreciate your help,

David

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 municipality str5 party byte is_council_member
"BRA " "CONS " 1
"COL " "LIB "  0
"BRA " "CONS " 1
"COL " "LIB "  1
"BRA " "LIB "  1
"COL " "CONS " 1
end

by municipality party, sort: egen wanted = total(is_council_member)

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[David Castro Pena]

HI Clyde,

Absolutely. I just installed dataex

Thanks for you help. Your response was helpful. Best

David