Generate average of each column conditional on the row value

[Alberto Alvarez]

Dear Stata Users,

Can you please help me with the following issue.

clear

* Define the dataset
input str3 Location march1990 apr90 may1990
USA 10 20 30
UK 11 12 13
SWE 2 3 4
End

I have the above database (with many more columns). I need to create a database like the one below, where NOR is the average of rows that have “Location” equal to USA and UK

input str3 Location march1990 apr90 may1990
USA 10 20 30
UK 11 12 13
SWE 2 3 4
NOR 10.5 16 21.5
End

Thank you!

[Clyde Schechter]

This is not hard to do:

tempfile copy
save `copy'
keep if inlist(Location, "USA", "UK")
ds Location, not
collapse (mean) `r(varlist)'
gen Location = "NOR"
order Location, first
append using `copy'


list, noobs clean

But, don't do it! Data sets that contain both item-level data and aggregated data in different observations are a recipe for trouble in Stata. It is clear that any calculations you do with the data set at that point need to exclude the "NOR" observation that was created to avoid indirectly double-counting the US and UK data. But sooner or later, you will forget to exclude it in some calculation and from that point on everything you have will be wrong.

Also, isn't NOR the ISO3 code for Norway? So why would Norway data be the mean of US and UK?

[Alberto Alvarez]

Thank you for your reply, the suggested solution worked. Regarding your question, NOR was just an example.