Help with coding of a variable

[Lisa Scheu]

I have a dataset containing 952 obs. Each observations contains two options people during a survey were presented, such as G1 & G2 or G2 & G3 etc. The respondents had to choose between the two. The variable comb_G contains the combination. G1_win is a dummy for all G1 wins (1) and losses (0). Down below you can see the wins and losses of G1 per combination. Now I woudl like to construct a variable (or two) which counts the total wins and losses. That is, a variable saying 5 wins and one saying 3 losses.

I am struggeling to find a way to do it ...

Any help would be greatly appreciated!!


bysort comb_G: tab G1_win

-------------------------------------------------------------------------------------
-> comb_G = G1G2

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 6 23.08 23.08
1 | 20 76.92 100.00
------------+-----------------------------------
Total | 26 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G3

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 13 46.43 46.43
1 | 15 53.57 100.00
------------+-----------------------------------
Total | 28 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G4

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 23 74.19 74.19
1 | 8 25.81 100.00
------------+-----------------------------------
Total | 31 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G5

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 9 33.33 33.33
1 | 18 66.67 100.00
------------+-----------------------------------
Total | 27 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G6

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 10 37.04 37.04
1 | 17 62.96 100.00
------------+-----------------------------------
Total | 27 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G7

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 15 55.56 55.56
1 | 12 44.44 100.00
------------+-----------------------------------
Total | 27 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G8

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 19 67.86 67.86
1 | 9 32.14 100.00
------------+-----------------------------------
Total | 28 100.00

-------------------------------------------------------------------------------------
-> comb_G = G1G9

G1_win | Freq. Percent Cum.
------------+-----------------------------------
0 | 4 20.00 20.00
1 | 16 80.00 100.00
------------+-----------------------------------
Total | 20 100.00

[Andrew Musau]

Welcome to Statalist. Probably best if you provided a data example, e.g., by copying and pasting the result of the following:

dataex in 1/30

[Lisa Scheu]

* Example generated by -dataex-. For more info, type help dataex
clear
input str36 key str2(g_mostrada_1 g_mostrada_2) str23 exp2_g str4 comb_G str23 nar_g float(G1_win G2_win G3_win G4_win G5_win G6_win G7_win G8_win G9_win)
"d2bba07c-aa64-4ed5-a87e-de82be9eff35" "G2" "G1" "[Value of G_MOSTRADA_1]" "G1G2" "G2" 0 1 . . . . . . .
"a4088d51-e788-47cb-a70b-7da5aa7adc37" "G1" "G2" "[Value of G_MOSTRADA_2]" "G1G2" "G2" 0 1 . . . . . . .
"176fce18-3369-4d9a-bc25-5d1cb9bda2cd" "G2" "G1" "[Value of G_MOSTRADA_1]" "G1G2" "G2" 0 1 . . . . . . .
"9d53a265-210b-4b37-847c-cc0835586f74" "G2" "G1" "[Value of G_MOSTRADA_1]" "G1G2" "G2" 0 1 . . . . . . .
"5dff0ba8-2863-4b0a-9cdc-1616dcce74a5" "G1" "G2" "[Value of G_MOSTRADA_2]" "G1G2" "G2" 0 1 . . . . . . .
"f6a12bfc-9610-4b84-8ca5-da0a143b2c3b" "G2" "G1" "[Value of G_MOSTRADA_1]" "G1G2" "G2" 0 1 . . . . . . .
"60afa182-351e-4c8a-a068-0b3a0b798def" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"66926762-4d3f-4fdf-b159-4f05c3e8e019" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"efb2d891-9dd8-46a9-80fc-c759d6af21db" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"7825faba-eb4f-4c66-96e3-a6ac9440e692" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"2ab3bf8e-b0df-489e-9c68-bca68e249f32" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"4550cd07-547e-4e76-bfa8-70b3f3179060" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"7fdc7b9e-31d3-489a-bf40-50632dfd23e2" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"f23e15dd-0972-4466-b402-99d72855e540" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"5dbaa0d9-74d9-4bb1-88de-7f4d36310970" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"8eda8c70-f81d-41fd-b745-c1dee9066e20" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"5cc8dede-9a70-4146-acbf-f34f54480f98" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"2d15df0c-b568-400d-aea4-e6c5d889428d" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"7da6c51c-726b-47d3-951d-c3d694e92e3c" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"6bc8899a-8ce2-4b29-8009-0ee4fdd072b7" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"6c65a681-38d2-4206-9894-b825cbfa98a8" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"6687dc32-a733-45d6-9343-5cdff9cff5fa" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"dcb85a95-e49a-48ac-8a12-13cbc0234d4f" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"ba2c5084-384e-4f5e-a819-2d49bcacfddd" "G1" "G2" "[Value of G_MOSTRADA_1]" "G1G2" "G1" 1 0 . . . . . . .
"76b6a8f8-62a6-4382-b834-6f5f4d7b04f9" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"af1fcac1-6296-4d51-be35-362568b8a5cb" "G2" "G1" "[Value of G_MOSTRADA_2]" "G1G2" "G1" 1 0 . . . . . . .
"7ce5e3fc-b447-460c-bd75-cde5e5c3851e" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"3e3761ec-0a74-4dea-8a51-ef14513df0f3" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"94e0a166-dd23-4e32-bb6b-6151276d6b01" "G3" "G1" "[Value of G_MOSTRADA_1]" "G1G3" "G3" 0 . 1 . . . . . .
"f843b9be-fd7e-4b75-a07b-297e22140aed" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"1230f523-d122-47d4-9200-fa0003782765" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"1f260758-e639-43fa-b9bd-02dfec3eae76" "G3" "G1" "[Value of G_MOSTRADA_1]" "G1G3" "G3" 0 . 1 . . . . . .
"b820c932-88c7-404f-aab1-a5285d330195" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"a061830b-adcd-44c4-891d-e31e64222769" "G3" "G1" "[Value of G_MOSTRADA_1]" "G1G3" "G3" 0 . 1 . . . . . .
"bd1ca0cc-e833-4c8e-b0e9-024fc8832ed7" "G3" "G1" "[Value of G_MOSTRADA_1]" "G1G3" "G3" 0 . 1 . . . . . .
"9694b1c2-6ad1-4c4c-804c-0c164154f504" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"a6593917-a90c-4196-9c1e-d7a89e2e8679" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"a3cd2eac-c5c6-4ae2-845a-9ee1b88d6e99" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"4119599e-74fb-4f68-bb53-fc15ac2dcaaf" "G1" "G3" "[Value of G_MOSTRADA_2]" "G1G3" "G3" 0 . 1 . . . . . .
"49e70a81-83d0-4b05-8845-13b3363edef2" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"73c48520-9bff-4e60-b018-bfed1fa36e09" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"2027e052-6230-4d0b-9f4e-118f3471836d" "G3" "G1" "[Value of G_MOSTRADA_2]" "G1G3" "G1" 1 . 0 . . . . . .
"f43c5c39-2633-4c5d-812d-17d9f268c683" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"fc160a64-98fa-4b72-a2df-431988d68925" "G3" "G1" "[Value of G_MOSTRADA_2]" "G1G3" "G1" 1 . 0 . . . . . .
"48978728-9875-4560-806a-02bf49dabd07" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"2164d8cc-2dad-4371-a1f1-b13c2ef9ca28" "G3" "G1" "[Value of G_MOSTRADA_2]" "G1G3" "G1" 1 . 0 . . . . . .
"736e7fb6-7d5d-42e3-9ff6-b10eaf1ef26e" "G3" "G1" "[Value of G_MOSTRADA_2]" "G1G3" "G1" 1 . 0 . . . . . .
"3c22962d-a113-4a68-8284-759507f51829" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"ecc51486-f62b-4a6e-b198-7e5bb4d6a103" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"433ce228-c4a4-47ca-8d6e-9d0e9bc7a8ef" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"7cdaa61a-b2a3-4e0b-94c9-0cb89ab0f77a" "G3" "G1" "[Value of G_MOSTRADA_2]" "G1G3" "G1" 1 . 0 . . . . . .
"6736b767-3e0a-4fbc-ae06-c8f4fe0751c1" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"c307da71-3a26-4b44-b8cf-90c7b2d5d85e" "G3" "G1" "[Value of G_MOSTRADA_2]" "G1G3" "G1" 1 . 0 . . . . . .
"723011e9-0cac-4e39-bc4f-4414b4e1efe3" "G1" "G3" "[Value of G_MOSTRADA_1]" "G1G3" "G1" 1 . 0 . . . . . .
"46fb4ad2-8d9c-4803-a4bc-6d1c50fabfdf" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"b939b93f-87f9-4fc4-be94-1a49cd224b31" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"b0ae463f-2b67-4dd7-9410-76fee27a102b" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"9b002d54-d37a-426e-8a13-2cc191557dbe" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"8e13dc23-b797-4c0f-93d9-f781a468e0d7" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"74c8d271-3f07-40f2-9854-16ec24ea243f" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"a099fd48-536a-4328-897d-8654651f6839" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"fb1d5da8-05bd-4566-a83e-fc5035c28b9f" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"57f1d324-62a1-4b8c-ace3-18789c697f9e" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"1428d476-1753-4718-b2ec-31ede3921078" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"49981578-b713-4ef1-9564-a15bcfb3e78b" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"3671f362-22be-4848-8e0d-35bc06f393a0" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"4f9a7920-e370-4cf7-b851-7df8b58aac61" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"56e73dc1-49ec-456e-9c92-5acc6e0e9971" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"46bbdf95-8b62-4233-a51f-eb29e91bb7ae" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"20d53115-b901-4cbb-87f8-99a401917eb8" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"6132c038-b8c5-4e5f-8593-aacb877f43ab" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"227dad7e-3cdb-465d-b66e-07a4d1f4e0c2" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"84f5055c-69dc-4edd-bef0-f8287edf6b08" "G1" "G4" "[Value of G_MOSTRADA_2]" "G1G4" "G4" 0 . . 1 . . . . .
"842ec792-e836-450a-a05a-02856eb9a8b9" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"f93e57ce-d12e-4edf-8ee7-8f1ef025ecdf" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"ae98fc4d-54cc-4b90-8800-2d345a553f78" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"bf02af8c-13e3-4b5c-a04c-fc88f1b208fe" "G4" "G1" "[Value of G_MOSTRADA_1]" "G1G4" "G4" 0 . . 1 . . . . .
"3085aff3-1905-4c19-9427-35058a085a88" "G4" "G1" "[Value of G_MOSTRADA_2]" "G1G4" "G1" 1 . . 0 . . . . .
"6a112023-e105-4bb9-a698-1dbcd68ed5c2" "G1" "G4" "[Value of G_MOSTRADA_1]" "G1G4" "G1" 1 . . 0 . . . . .
"3de39848-66a1-4cfd-966f-1d5a58c76af0" "G4" "G1" "[Value of G_MOSTRADA_2]" "G1G4" "G1" 1 . . 0 . . . . .
"9c589ecf-a702-4835-a445-ef598d6cbd6f" "G4" "G1" "[Value of G_MOSTRADA_2]" "G1G4" "G1" 1 . . 0 . . . . .
"6cbb736f-c287-4145-8516-77c254644dc9" "G4" "G1" "[Value of G_MOSTRADA_2]" "G1G4" "G1" 1 . . 0 . . . . .
"272b5b74-4dac-42b3-ad9e-26eca8385931" "G1" "G4" "[Value of G_MOSTRADA_1]" "G1G4" "G1" 1 . . 0 . . . . .
"973930d2-5e4e-4983-aeb4-1573a0c34126" "G4" "G1" "[Value of G_MOSTRADA_2]" "G1G4" "G1" 1 . . 0 . . . . .
"e0ed9956-b159-4df9-8917-3d965b6b6fb7" "G1" "G4" "[Value of G_MOSTRADA_1]" "G1G4" "G1" 1 . . 0 . . . . .
"41fd1033-d75e-496d-b4ff-1339c8415d56" "G1" "G5" "[Value of G_MOSTRADA_2]" "G1G5" "G5" 0 . . . 1 . . . .
"281ab9ff-4824-4cb5-8fbd-20c438aca11d" "G5" "G1" "[Value of G_MOSTRADA_1]" "G1G5" "G5" 0 . . . 1 . . . .
"a9264b11-00ec-460e-bf72-1a4dc900db9d" "G5" "G1" "[Value of G_MOSTRADA_1]" "G1G5" "G5" 0 . . . 1 . . . .
"62fa7a1e-8934-4b28-b594-ce9639180275" "G5" "G1" "[Value of G_MOSTRADA_1]" "G1G5" "G5" 0 . . . 1 . . . .
"572a57fb-9b40-4461-b598-3bda35185fa4" "G1" "G5" "[Value of G_MOSTRADA_2]" "G1G5" "G5" 0 . . . 1 . . . .
"b65f0c1b-4bf4-44cc-b3c6-c5d4c833e1d9" "G1" "G5" "[Value of G_MOSTRADA_2]" "G1G5" "G5" 0 . . . 1 . . . .
"8a4042a5-2a1d-4bcc-b252-0d707dc2a470" "G1" "G5" "[Value of G_MOSTRADA_2]" "G1G5" "G5" 0 . . . 1 . . . .
"3145010b-94d3-4330-9f3f-f63da6227357" "G1" "G5" "[Value of G_MOSTRADA_2]" "G1G5" "G5" 0 . . . 1 . . . .
"e16390b3-c374-4d41-a09e-aa76fcdb24b4" "G5" "G1" "[Value of G_MOSTRADA_1]" "G1G5" "G5" 0 . . . 1 . . . .
"107a0eaa-c3b3-4aa3-bcbf-96b727d2254e" "G1" "G5" "[Value of G_MOSTRADA_1]" "G1G5" "G1" 1 . . . 0 . . . .
"39ebbf0a-41e6-40b5-9655-5f869b8bc028" "G5" "G1" "[Value of G_MOSTRADA_2]" "G1G5" "G1" 1 . . . 0 . . . .
"93ad3001-ff34-46dd-81cc-ef3497f14036" "G5" "G1" "[Value of G_MOSTRADA_2]" "G1G5" "G1" 1 . . . 0 . . . .
"a9adc411-392b-445f-bfb5-e2b56c96a491" "G1" "G5" "[Value of G_MOSTRADA_1]" "G1G5" "G1" 1 . . . 0 . . . .
"b88f7b1c-8a67-4a7d-a8ff-98f4817b9145" "G1" "G5" "[Value of G_MOSTRADA_1]" "G1G5" "G1" 1 . . . 0 . . . .
"42129644-897e-4e26-a0c7-1cf66a1bebb3" "G1" "G5" "[Value of G_MOSTRADA_1]" "G1G5" "G1" 1 . . . 0 . . . .
end

[Lisa Scheu]

Basically people in the survey were presented a choice of two options and they had to choose one. I would like to construct a variable which shows how many times each category won and lost.
In the attachmetn you can see how many times the option G1 won when presented alongside G2, G3 etc. For the first combination (G1 and G2) it shows that it won 20 times and lost 6 times. In the second one 15 times and lost 13 etc.. This goes on for all 8 possibel combinations. Now I want to show how many times each option won aggregated across the combinations... Therefore for G1 it should be 5 times, because in 5 categries the wins exceeded the losses
... I am a bit lost on how to do it.. Maybe it is not possible?



I would be grateful for any hints!

[Hemanshu Kumar]

Perhaps something like this?

egen byte tag = tag(comb_G)
forval i=1/9 {
    egen G`i'_wins_in_combo = total(G`i'_win == 1), by(comb_G)
    egen G`i'_loses_in_combo = total(G`i'_win == 0), by(comb_G)
    egen G`i'_wins = total((G`i'_wins_in_combo > G`i'_loses_in_combo)*tag)
    egen G`i'_loses = total((G`i'_wins_in_combo < G`i'_loses_in_combo)*tag)
    egen G`i'_ties = total((G`i'_wins_in_combo == G`i'_loses_in_combo & G`i'_wins_in_combo != 0)*tag)
    drop G*_in_combo
}
drop tag

[Lisa Scheu]

This is marvellous - thank you!
very helpful