Hello Statalist,
I have a dataset which contains the following variables: firm (every firm has an assigned number from 1-1000), their products (every product has an asigned number), the costs of sales, the revenue of the sale, and the year. Now I am trying to generate two very similar variables and a third one. One displays the average sales for each year and each firm and one displays the average costs for each year and each firm. The code should be equal for both, simply exchanging sales with costs. Now I am trying to construct this variable using a loop but I don't get the right result. My first try was the following:

forval i = 1/1000 {
forval j = 2001/2012 {
sum ventas if firma == `i' & year == `j'
gen ventap_`i'_`j' = `r(mean)'
}
}



There are 1000 firms. However the year data is not equal for all firms. There are some firms who have data from for example 2004-2009 or others with different periods (a lot of different periods) But the min of the variable year is 2001 and the max is 2012.

So when I run this code I encounter 2 problems: first it doesnt work because the firm doesnt have any observations for the year 2012 or other years (invalid syntax error). Second it creates a variable for every single year, displaying the average for that year. However I want just one variable displaying the average for the corresponding year for all cases.

The third variable that I have to create is one that displays the product that has the highest sales. The code should be a similar one to the first one, using 2 forvals containing the year and the firm but instead of using r(mean) it should probably use r(max). However here I encounter the same problem that not all firms have data for all the years between 2001 and 2012 and it generates a lot of variables instead of just one which shows the product id with the highest sales for the corresponding year.

I Hope i explained it understandibly and you can help me.
Thanks a lot