How can I use egen to sum the values of one variable for each instance when other variable is some value?

[Krista Lane]

My original data set has three variables:

year, country, quantity

In my code, I create other variables named after the values of the "country" variable (China, India, etc.). I would like to define these variables in a way so that I may ultimately be able to drop "quantity", "country", and duplicates and just have the total quantity for each country in the given year.

I tried to populate these variables with the sum of the "quantity" variable for each year and country. I know the problem is in the line with bysort (if country=="`lev'"), but I don't know how to fix it. I tried:

levelsof country
foreach lev in `r(levels)' {
bysort country year: egen `lev' = total(quantity) if country=="`lev'"
}

This creates something like:

year	country	quantity	India	China	US
2000	India	1	1	.	.
2001	India	2	5	.	.
2001	India	3	5	.	.
2000	China	4	.	4	.
2004	China	5	.	5	.
2007	China	6	.	6	.
2001	US	7	.	.	7
2006	US	8	.	.	8

I would like it to create something like this:

year	country	quantity	India	China	US
2000	India	1	1	4
2001	India	2	5	.	7
2001	India	3	5	.	7
2000	China	4	.	4
2004	China	5	.	5
2007	China	6	.	6
2001	US	7	.	.	7
2006	US	8	.	.	8

[Sergio Correia]

You might want to use a combination of -reshape- and -merge-

[Krista Lane]

How would that work?

[Sergio Correia]

Like this:

cls
clear all
input int year str20 country int quantity
2000 India 1
2001 India 2
2001 India 3
2000 China 4
2004 China 5
2007 China 6
2001 US 7
2006 US 8
end

preserve

encode country, gen(cou)
drop country
collapse (sum) quantity, by(year cou)
reshape wide quantity, i(year) j(cou)
tempfile collapsed
save "`collapsed'"

restore
merge m:1 year using "`collapsed'", nogen
list

Basically, collapse + reshape and then merge back the data. I didn't rename the variables back to "India", etc. because that is a bit fragile (you can't have "South Africa" as a variable). In any case, you can use -levelsof- to store the levels and then rename them.

[William Lisowski]

Sergio demonstrates good technique for getting your data into the shape you desire. I'm going to suggest that you consider stopping after the collapse command. At that point, your data is in what is often called a "long layout". After the reshape, your data is in a "wide layout". If you've ever built Excel pivot tables, you'll recognize long layout as what you need to make pivot tables work most effectively, so that you can easily filter on countries or years, or summarize within years or within countries.

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 rather than a wide layout of the same data.

You perhaps are familiar with the distinction between long and wide layout already - but your question in #3 makes me think you might not be. Of course, without knowing how this data fits into the larger plan for your analysis, I don't know if my advice is at all appropriate. Please know that it is well meant, and at least keep it in mind if you find yourself having problems accomplishing what you need with the layout you describe.

[William Lisowski]

Looking a little more closely at your original post, for what it's worth, here's how to accomplish what you wanted using egen. Starting with your original data in memory (from Sergio's post):

// remember the original order
gen seq = _n

levelsof country
foreach lev in `r(levels)' {
bysort year: egen `lev' = total(cond(country=="`lev'",quantity,.)), missing
}

// resort into original order
sort seq
drop seq
list, clean noobs

which gives

    year   country   quantity   China   India   US  
    2000     India          1       4       1    .  
    2001     India          2       .       5    7  
    2001     India          3       .       5    7  
    2000     China          4       4       1    .  
    2004     China          5       5       .    .  
    2007     China          6       6       .    .  
    2001        US          7       .       5    7  
    2006        US          8       .       .    8

[Krista Lane]

Thank you so much for both of your answers. I tried them both and they have the same result.

You're right that I don't have much experience with long or wide forms of data (or reshaping in general).In this case, I'm trying to format the data in a way that will make it easier to build a stacked bar chart (with year on the horizontal axis, quantity on the vertical and different colored bars for each country).

I have a few questions so that I can apply your answers to my future work:

• First, I've never used macros to merge datasets (I've always saved them as .dta files). Can you only do this within a preserve-restore section? If so, is it possible to do this multiple times (several preserve-restore sections) and have multiple "macro" datasets stored at once?
• In William's code, what does "list, clean noobs" do?

Thanks again!

[William Lisowski]

In William's code, what does "list, clean noobs" do?

It produces the output displayed in the second Code block of my post. Compared to list with no options, the clean option suppresses the box drawn around the data, and the noobs option suppresses listing the observation number.

To understand what a particular command does - in this case, the list command - the best place to start is with Stata's online help for that command. Typing

help list

into Stata's Command window and looking at what Stata displays as a consequence will set you in the right direction to understanding the command.

As more general advice, when I began using Stata in a serious way, I started 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. All of these manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through Stata's Help menu. The objective in doing this 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.

Stata supplies exceptionally good documentation that amply repays the time spent studying it.

Regarding getting your data in shape for graphing, I'm not an expert on Stata graphics. My Excel background lets me understand, I think, where your thought that you need separate variables for each country arises. But graphing in Stata is much more like producing a pivot chart from a pivot table in Excel. A quick glance at the documentation for the graph bar command given in the Stata Graphics Reference Manual PDF suggests that something like

graph bar quantity, over(country) asyvars

may be a starting point to a stacked bar graph. Or may not, I didn't look beyond the third page of the documentation.

You might consider starting by reading the documentation for graph bar and working through the examples to see how the data is structured to produce the sort of graph you are looking for, and then using that to inform how you structure your data. It's probably a lot easier to beat data into the structure that makes producing your graph easy, than it is to beat the graph bar command into producing your graph from inappropriately structured data.

Added in editing: You should look at help tempfile to understand that Sergio's code is indeed saving the data in a .dta file - one that is temporary and will disappear when the do-file, or Stata session, comes to an end.