One line code for dividing all countries by one specific country

[Aishwary Trivedi]

I need to divide all countries HS code values by Australia. What code will be the most efficient one. For ex: 1996 HS-56 Argentina/Australia, Brazil/Australia etc.
1997 HS-56 Argentina/Australia, Brazil/Australia etc

Year	Product/Sector	Partner Economy	Albania	Antigua and Barbuda	Argentina	Armenia	Australia	Bahrain, Kingdom of	Bangladesh	Barbados	Belize	Benin	Bolivia, Plurinational State of	Botswana	Brazil	Brunei Darussalam	Bulgaria	Burkina Faso	Burundi	Cabo Verde	Cambodia	Cameroon	Canada	Central African Republic	Chad	Chile	China	Colombia	Costa Rica	Côte d'Ivoire	Croatia	Cuba	Cyprus	Czech Republic
1996	HS - 56 - Wadding, felt and nonwovens; special yarns; twine, cordage, ropes and cables and articles thereof	United States of America			7630697		24338517						247820	141	19526014	391380							226839036			8130000	9763768	9725028	2969149				140272	752856
1996	HS - 57 - Carpets and other textile floor coverings	United States of America			8282674		6285057						896034	2675	9376046	819330							279687195			13241000	16755963	8385541	1935851				1155290	3740878
1996	HS - 58 - Special woven fabrics; tufted textile fabrics; lace; tapestries; trimmings; embroidery	United States of America			1274138		8181001						211534	311	9955995	81635							67017075			2453000	10142966	9181513	1839854				34667	237033
1996	HS - 59 - Impregnated, coated, covered or laminated textile fabrics; textile articles of a kind suitable for industrial use	United States of America			6387767		33775076						609932	4552	13650407	62885							328955983			11274000	10236808	4316928	7538430				565357	2668351
1996	HS - 60 - Knitted or crocheted fabrics	United States of America			1745076		13084867						95655		4192538	4390							158197352			4605000	4935681	3063248	1791528				410251	361634
1996	HS - 61 - Articles of apparel and clothing accessories, knitted or crocheted	United States of America			4591744		16640538						273764	12996	13301963	147842							232811555			12699000	2354748	17812075	2538311				607932	2062959
1996	HS - 62 - Articles of apparel and clothing accessories, not knitted or crocheted	United States of America			4446491		15286958						462590	29170	22450725	156128							204095412			13977000	5502583	21689757	4746830				482445	1229427
1996	HS - 63 - Other made up textile articles; sets; worn clothing and worn textile articles; rags	United States of America			5629742		18320218						2067094	39047	10298163	475186							222062647			17811000	3142956	3197454	5608095				377561	660782
1996	HS - 64 - Footwear, gaiters and the like; parts of such articles	United States of America			1486650		9748083						326477	7278	8485298	296971							61395204			3499000	38244373	4366029	5233613				410243	1518121
1996	HS - 65 - Headgear and parts thereof	United States of America			731025		6123857						41718	21966	2510002	23449							26946339			1533000	100614	991637	348333				68112	338178
1996	HS - 66 - Umbrellas, sun umbrellas, walkingsticks, seatsticks, whips, ridingcrops and parts thereof	United States of America			127072		187119						8217		367814	5417							2784243			68000	129390	345513	142167				5533	16174
1996	HS - 67 - Prepared feathers and down and articles made of feathers or of down; artificial flowers; articles of human hair	United States of America			317124		985994							494	2531689	1354							2525756			407000	1261890	508604	153343				532	198130
1996	HS - 68 - Articles of stone, plaster, cement, asbestos, mica or similar materials	United States of America			8100489		31354985						491843	3235	21324335	437414							272115716			13089000	22684128	8454021	3364568				538674	1877841
1996	HS - 69 - Ceramic products	United States of America			5699289		14545503						739156	11239	18627596	791809							163383621			8728000	25884207	13228941	1894397				59556	160221
1996	HS - 70 - Glass and glassware	United States of America			14662399		44923577						2087869	953282	46523071	542481							982754104			13533998	64061878	18604452	7271796				550126	2119648
1996	HS - 71 - Natural or cultured pearls, precious or semiprecious stones, precious metals, metals clad with precious metal, and articles thereof; imitation jewellery; coin	United States of America			1272741		24343781						41769152	909605	6313708	6886							765519858			1481000	32237564	2677443	17810640				228745	1737696
1996	HS - 72 - Iron and steel	United States of America			11713446		30304400						433075	118	34505558	627598							1449433475			8324000	105430529	65014606	10140784				54930	335416

[Nick Cox]

People often ask for "efficient" code but almost never define what that means, e.g. user typing time, machine time, storage implications, absence of side-effects to dataset, ease of debugging or of writing to custom.

There is a certainly a one-line solution in that you could write a program to do this when called. Otherwise

1. Your data example is not one I can use easily as you did not use dataex as requested.

2. There is a two-line solution I can think up that follows from principles discussed in many places, such as https://www.stata-journal.com/articl...article=dm0055

sysuse census, clear
egen reference = total((state == "Texas") * medage)
gen wanted = medage / reference
quantile wanted

The egen solution is one I push here because it creates a reference variable. That isn't needed here because the reference level is a single constant that you could get from summarize or just by accessing a data value directly. But you will need a reference variable if for example you have panel data, and the solution then extends painlessly to say

egen reference = total((state == "Texas") * medage), by(year)

The paper linked above covers several variations on this question.

[William Lisowski]

Actually, I believe your failure to present a useful data example has hidden the meaning of your question.

I think you mean that you want to divide each of the columns headed "Antigua and Barbuda", "Argentina", "Armenia", "Australia", "Bahrain, Kingdom of" ... by the value in the column headed "Australia".

If you had a Stata dataset with variables ant, bear, cat, dog, ... zebra and you wanted to divide each variable by the value of the variable dog the following loop would do what you need.

foreach v of varlist ant-zebra {
    replace replace `v' = `v'/dog
}

But your data are currently in a wide layout, with separate variables for each country. The experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data - in this case, separate observations for each country - rather than a wide layout of the same data.

generate seq = _n
rename (ant-zebra) (value=)
reshape long value, i(seq) j(beast) string

after which the code outlined in post #2 will do what you need.

egen reference = total((beast == "dog") * value), by(seq)
generate ratio = value/reference

Finally, let me repeat, in different words, advice you were given on a previous topic and did not heed when posting this topic.

Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

If you are asking for help with Stata code, you should start by providing an example of your Stata data. Not of your Excel spreadsheet, or whatever, that has yet to be read into Stata and assigned actual variable names. What you posted is not of use creating and testing Stata code. For that reason all the code I gave above is untested and may require debugging correction by you before it works successfully for you.

[Funsho Obakemi]

Dear Scholars,
I have a panel data set of 106 countries for the period of 2007-2020. I intend to divide the countries into quantiles with respect to a variable. Is there stata command? How to i go about this.
Table below is a hypothetical example of what i intend to do

Quantile	Countries
The lower 10th (0 < quantile < 10)	Iceland, Sweden, and Finland
The 10–25th (10 _< quantile < 25)	Ireland, Norway, Estonia, and Austria
The 25–50th (25 <_ quantile < 50)	France, Lithuania, Luxembourg, the United Kingdom, Denmark, Spain, and Portugal
The 50–75th (50 <_ quantile < 75)	Romania, Cyprus, Greece, Latvia, Belgium, Germany, and the Netherlands
The 75–90th (75 <_ quantile < 90)	Croatia, Czechia, Slovenia, Hungary, and Italy
The upper 90th (quantile >_ 90)	Poland, Bulgaria, and Slovakia

[Nick Cox]

#4 isn't related to the thread title. Here is some technique.

sysuse auto, clear


gen wanted = 6 if price < .
tokenize 10 25 50 75 90
summarize price, detail
forval p = 5(-1)1 {
   replace wanted = `p' if price < r(p``p'')
}
 
tabstat price, by(wanted) stat(count min max)
 
quantile price, ms(none) mla(wanted) mlabpos(0)

[Funsho Obakemi]

Prof Nick Cox
Pls i need further clarification.
i dont understand the code

[Funsho Obakemi]

If you don't mind
i will appreciate conversation via email

[Nick Cox]

I have two problems here. I don't understand the code is not specific enough for me to answer and I will appreciate conversation via email is contrary to our request that you don't ask for private answers..

If you want a private tutor or consultant, then it is easiest to find people offering that on social media. The people answering many questions here are on average vastly more experienced but they don't operate as tutors or consultants.

[Dirk Enzmann]

If you don't understand any code (syntax, command), the best you can do before asking here: Take the commands one after another that have been suggested and use Stata's excellent help, for example

help tokenize

Note that from there you can always jump to Stata's PDF manual that is extremely well written and often will show you examples not available in the immediate help. While reading: Open a new Stata session, load the data suggested in the examples (or use the famous auto data set of Stata's example data) and experiment with the examples you find.

It may be that you think that this is inefficient (it would take more effort and time), but in the long run this will pay off (and it is the only way to become an experienced Stata user). Plus: The Stata Forum is tremendously helpful (and friendly!), hence you should appreciate this by not trying to let others do your work (such as reading the manual).

[Dirk Enzmann]

For those who may struggle to understand the output (more likely the table than the helpful graph) of the solution suggested in #6: Add the following commands to the syntax suggested and run them

lab def wanted 1 "[p0-p10[" ///
               2 "[p10-p25[" ///
               3 "[p25-p50[" ///
               4 "[p50-p75[" ///
               5 "[p75-p90[" ///
               6 "[p90-p100]"
lab val wanted wanted
tabstat price, by(wanted) stat(count min max)

Result:

. tabstat price, by(wanted) stat(count min max)

Summary for variables: price
Group variable: wanted

    wanted |         N       Min       Max
-----------+------------------------------
  [p0-p10[ |         7      3291      3829
 [p10-p25[ |        11      3895      4187
 [p25-p50[ |        19      4195      4934
 [p50-p75[ |        18      5079      6303
 [p75-p90[ |        11      6342     10372
[p90-p100] |         8     11385     15906
-----------+------------------------------
     Total |        74      3291     15906
------------------------------------------

But as Nick Cox already said: #4 is not related to the thread (Funsho Obakemi has the habit to ask questions not related to current threads although being reminded not to do so several times), therefore I will not continue this discussion.

[Funsho Obakemi]

Am very sorry if i had violated the rule of the system. I guess am yet to under the platform well. Pls pardon my habit. Thanks