Generate sum of variable by two other variables in two-dimensional panel framework

[Ingo Geburtig]

Dear Statalist,

I use Stata 15 on a Windows device and have a dta.-file with two non-overlapping panel dimensions (e.g. var1: A-D and var2: E-H). For each possible combinations from var1 and var2 there is a maximum of one observation (sometimes there could also be no observation), which has one integer (var4) and one dummy variable (var3). I want to generate a new variable, that, for each row n, sums the values of var4 for all observations of var1, that have a 1-Dummy in their observation pair with the var2 value from row n. I want to illustrate the problem with the following data example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1(var1 var2) float(var3 var4 var5)
"A" "E" 1 3 18
"A" "F" 1 5 18
"A" "G" 0 8 18
"A" "H" 1 2 18
"B" "E" 1 5 26
"B" "F" 0 9 26
"B" "G" 0 4 26
"B" "H" 0 8 26
"C" "E" 0 2 16
"C" "F" 0 3 16
"C" "G" 0 4 16
"C" "H" 1 7 16
"D" "E" 0 3 11
"D" "F" 0 3 11
"D" "G" 0 1 11
"D" "H" 1 4 11
end

In the example, var5 shows the sum of var4 by var1. The variable I'm looking for should have a value of 44 (26+18) in lines 1,5,9 and 13 (var2=E) as E has a 1-Dummy with var1 values A and B (see row 1 and 5). For var2 values of F, G and H, the var1 value should be 18 (sum var4 if var1 = A), 0 (no 1-Dummy) and 45 (sum var4 if var1 = A, C or D) respectively.

Kind regards
Ingo

[Clyde Schechter]

by var2 (var1), sort: egen wanted = total(cond(var3 == 1, var5, .))
sort var1 var2

[Ingo Geburtig]

That works perfect. Thanks a lot!