Generate variables with forvals

[Lee Wilson]

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 id number), the costs of sales (of the product), the revenue of the sale (of the product), and the year (to which the data corresponds). 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

[William Lisowski]

Welcome to Statalist.

I think you need a different approach.

by firm year, sort: egen ventap = mean(ventas)

and the output of help egen will show you other functions besides mean() that will help with other calculations, while the output of help by will explain the basics of the by prefix.

Now, the by prefix is basic Stata, which leads me to think you're new to Stata. If not, please accept my apologies. Otherwise, some advice that may help you.

I'm sympathetic to you as a new user of Stata - it's a lot to absorb. And even worse if perhaps you are under pressure to produce some output quickly. Nevertheless, I'd like to encourage you to take a step back from your immediate tasks.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. There are a lot of examples to copy and paste into Stata's do-file editor to run yourself, and better yet, to experiment with changing the options to see how the results change.

All of these manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu. The objective in doing the reading was not so much to master Stata as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively

[Lee Wilson]

Hello William,
yes you're right I am new to Stata. Thanks for all the advice. I'll make use of it and try to start reading the Users Guide when I find some free time. But again, thanks a lot, you helped me a lot and it was the right solution to my problem!