Generate variable containing ratios of variable values per group

[Benedikt Walker]

Dear Forum,

I have the following very basic problem I`m struggling to resolve. Unfortunately I couldn`t find an appropriate solution in the forum, but probably I was just unable to transfer the right solution to my problem, so please correct me in case there`s a thread out there already.

My data is structured as follows:

State Citysize City
1 1 1
1 2 2
1 3 3
2 1 4
2 5 5
2 2 6
3 1 7
3 3 8

I want to generate a variable that contains the ratio of observations between all cities sized 1 and all cities sized 3 per state. Let`s call it ratiocs; that I could use for a table of state/ratiocs, showing me the ratio between city size 1 and city size 3 per state. Any help would be highly appreciated, thank you in advance,

Benedikt

[Nick Cox]

Presumably there is something else that you want the ratios of, but the question is still ambiguous. From your wording there could be one or more cities of size 1 and of size 3 in each state, so do you want e.g. ratios of means (totals, whatever) ?

Ratio of observations makes no sense in Stata terms, as observations are the rows, cases or records in the dataset. You probably mean data values but as above it's still not clear what you expect done with them.

Your example is a good start but you need to follow through and explain fully what you want.

[Benedikt Walker]

Dear Mr. Cox,

thank you for your fast reply and please excuse the ambiguous wording. I want to generate a variable expressing the ratio between the total of cities sized 1 and the total of cities sized 3 per state.
e.g.: let`s assume in state 1 there are 10 cities of size 1 and 15 cities of size 3. ratiosc would therefore be 10/15 in case of state==1.

I hope I could clarify my question? Thank you very much in advance!

[Nick Cox]

There is no need to address people with formal titles such as Mr in Statalist -- if only because people usually get them wrong in any case. Use the names people give themselves.

You don't want the totals, I think. You want the frequencies. So this may help. (As it turns out, totals of 0s and 1s is a good way to get the frequencies.)

bysort State: egen Total3 = total(Citysize == 3)
by State: egen Total1 = total(Citysize == 1)
gen Ratio = Total1/Total3
tabdisp State, c(Ratio)

For any further analysis downstream, watch out for multiple values. This should help

egen tag = tag(State)
list State Total? Ratio if tag

[Benedikt Walker]

Thank you very much, you got me right and solved my problem! Have a great day, you made mine much better!