Descending Order

[Paris Rira]

Dear Profs and colleagues,

Through this code I generate "share_immigrants_`country' "

local countries "AD AE AF AG AI AL AM AN AO AP AQ AR AS AT AU AW AX AZ BA BB BD BE BF BG BH BI BJ BL BM BN BO BQ BR BS BT BV BW BY BZ CA CC CD CF CG CH CI CK CL CM CN CO CR CU CV CW CY CZ DE DJ DK DM DO DZ EC EE EG EH ER ES ET FI FJ FK FM FO FR GA GB GD GE GF GG GH GI GM GN GP GQ GR GS GT GU GW GY HK HN HR HT HU ID IE IL IM IN IO IQ IR IS IT JE JM JO JP KE KG KH KM KP KR KW KY KZ LA LB LC LI LK LR LS LT LU LV LY MA MC MD ME MG MH MK ML MM MN MO MQ MR MS MT MU MV MW MX MY MZ NA NC NE NG NI NL NO NP NR NU NZ OM PA PE PF PG PH PK PL PM PN PR PS  PW PY QA RE RO RS RU RW SA SB SC SD SE SG SH SI SK SL SM SN SO SR SS ST SV SX SY SZ TC TD TF TG TH TJ TK TL TM TN TO TR TT TW TZ UA UG UM US UY UZ VA VC VE VG VN VU WF WS XK YE ZA ZM ZW"


// Loop through each country to generate shares
foreach country in `countries' {
    egen total_immigrants = total(nacio == "`country'"), by(NPC_FIC year)
    gen share_immigrants_`country' = (nacio == "`country'") / total_immigrants
    drop total_immigrants
}

* Example generated by -dataex-. For more info, type help dataex
clear
input double(year NPC_FIC) float(share_immigrants_CL share_immigrants_CM share_immigrants_DE share_immigrants_DK share_immigrants_DJ share_immigrants_DM share_immigrants_BR)

2010 500000284 . . . . . . .
2010 500000284 . . . . . . .
2010 500000305 . . . . . . .
2010 500000324 . 0.1 . . . . .
2010 500000334 . . . . . . .
2010 500000341 . . . . . . .
2010 500000341 . . . . . . .
2010 500000346 . . . . . . 0
2010 500000346 . . . . . . 1
2010 500000346 . . . . . . 0
2010 500000346 . . . . . . 0
2010 500000346 . . . . . . 0
2010 500000376 . . . . . . .
2010 500000376 . . . . . . .
2010 500000376 . . . . . . .
2010 500000376 . . . . . . .

..
2011 500000284 . . . . . . .
2011 500000284 . . . 0.5 . . .
2011 500000305 . . . . . . .
2011 500000324 . . . . . . .
2011 500000334 0.1 . . . . . .
2011 500000341 . . . . . . .
2011 500000341 . . . . . . .
2011 500000346 . . . . . . 0
2011 500000346 . . . . . . 1
2011 500000346 . . . . . . 0
2011 500000346 . . . . . . 0
2011 500000346 . . . . . . 0
2011 500000376 . . . . . . .
2011 500000376 . .0.2 . . . . .
2011 500000376 . . . . . . .
2011 500000376 . . . . . . .
...
end

I need to create a table that displays the share of immigrants for each country and year in descending order. For example, if Brazil consistently has the highest share of immigrants over the specified time period, it should be listed first in the table. The table will rank countries from the highest to the lowest share of immigrants. Data are worker-level data so firm id ( NPC_FIC) is repeated several times during a year because there are several workers in a firm in a year.
Any ideas appreciated.

Cheers,
Paris

[Mike Lacy]

If your data were in long format, with each observation representing a country-year. what you want this would be easy enough to do. In trying to create some example code for you for this purpose, I encountered a confusion: You say you want "the share of immigrants for each country and year," but you have calculated shares *within firm,* as indicated by your use of "by(NPC_FIC year)" in your calculation. You can't go from shares within firm to shares within country. So, to my (perhaps incorrect) understanding of what you have and what you want, you need to first calculate your share variables differently. If my understanding is correct, I'd suggest that you clarify your goal regarding the level of aggregation you want (country-year?), and post example data from which the country-years shares could be calculated. (You say that "Data are worker-level data" but without an example, understanding your situation is difficult.) The solution here *might* be as simple as changing your "by" specification, but I'm not sure.

Regarding sorting: The built-in -gsort- command allows descending order, which might be useful to you.

[Paris Rira]

Hi Mike, Thank you for getting back to me.
You are right in terms of "by(NPC_FIC year)" I just corrected to "by( year)" . Becasue Firm does not matter, I need to know which nationality has a great proportion during these 10 years(2010-2019). Moreover worker-level data means that one firm e.g firm id :500000284 comes twice a year. Because it has two workers.