sum by month and year

[Ritika Khurana]

Hi everyone,

I want to sum d1 by month and year but with a twist. I want to sum all the values from Oct (month = 10) of t-1 year to September (month = 9) of t year for all the counties. The data runs from 2000 to 2015 for all counties of US. In this example, you can only see one county but my dataset is really big. I tried running this in R and posted the question on stack overflow https://stackoverflow.com/questions/...73258_71135282, however, I still got errors that I couldn't understand. Since I am more comfortable with Stata so I switched to Stata.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long mapdate int year long fips str33 county str2 state float(none d0 d1 d2 d3 d4) byte month
20051227 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20051220 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20051213 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20051206 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20051129 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20051122 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20051115 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20051108 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20051101 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20051025 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20051018 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20051011 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20051004 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20050927 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20050920 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20050913 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20050906 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20050830 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20050823 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20050816 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20050809 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20050802 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20050726 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20050719 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20050712 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20050705 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20050628 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20050621 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20050614 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20050607 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20050531 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20050524 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20050517 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20050510 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20050503 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20050426 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20050419 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20050412 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20050405 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20050329 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20050322 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20050315 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20050308 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20050301 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20050222 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20050215 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20050208 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20050201 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20050125 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  1
20050118 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  1
20050111 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  1
20050104 2005 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  1
20041228 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20041221 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20041214 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20041207 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 12
20041130 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20041123 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20041116 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20041109 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20041102 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 11
20041026 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20041019 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20041012 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20041005 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0 10
20040928 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20040921 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20040914 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20040907 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  9
20040831 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20040824 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20040817 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20040810 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20040803 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  8
20040727 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20040720 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20040713 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20040706 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  7
20040629 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20040622 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20040615 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20040608 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20040601 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  6
20040525 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20040518 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20040511 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20040504 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  5
20040427 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20040420 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20040413 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20040406 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  4
20040330 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20040323 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20040316 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20040309 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20040302 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  3
20040224 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20040217 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20040210 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
20040203 2004 2013 "Aleutians East Borough" "AK" 100 0 0 0 0 0  2
end

Any help would be appreciated. Thank you!

Ritika

[Clyde Schechter]

So the collections of months that you want to sum over are actually 12 month consecutive blocks, except that instead of starting in January, they start in October. These are like fiscal years. Let's call them pseudo-years

//  CREATE A PSEUDO-YEAR THAT BEGINS IN OCTOBER
gen pseudo_year = cond(month >= 10, year, year-1)

//  SUM OVER PSEUDO YEARS
by state county pseudo_year, sort: egen total_d1 = total(d1)

In your example data you show only one state and one county. And I'm not certain from your description whether you want a single grand total for each pseudo-year encompassing all states and counties, or whether you want these done separately for every state, or perhaps for every state#county combination. The code above assumes the last. If that's wrong, just remove county or both state and county from the -by- prefix.

Also in your example data, d1 is always 0, so the results are uninteresting. Presumably there is really something worth adding up in d1 in the entire data set.

[Nick Cox]

See also https://www.stata-journal.com/articl...article=st0394 with a nice tip by Clyde Schechter towards the end.

[Ritika Khurana]

Thank you, Clyde Schechter and Nick Cox for your responses. I could work it out. Yes, it seems weird as to why am I trying to add up zeros here, but my data has values, except dataex doesn't produce those values for example.

[Ritika Khurana]

I would like to ask another doubt using the same dataex example. Now, I have to sum slightly differently than #1. I have to follow the following definition

drought variables are defined as the number of weeks that a county experiences a drought of a given severity level, where each week is by the percentage of the county’s agricultural area affected by drought during that week.

.

Given this definition, I tried calculating no. of weeks. What I have in the example d0-d4 is the percentage of the county’s agricultural area affected by drought.

I tried this:

forval i=0/4 {
gen w`i'= d`i'/100
}
by state county psyear, sort: egen dr0 = mean(d0)/anycountcount(w0)

this doesn't work.

Then, I generated a variable, week = _n.

But this variable doesn't help me calculate the no. of weeks weighted by the affected area.

Can someone please help me again?

Thank you,

Ritika

[Nick Cox]

I can't follow what you want here. but it is possible to explain why this command "doesn't work". (See FAQ Advice #12 on how this is not usually a good problem report.)

The relevant part of egen syntax

egen [type] newvar = fcn(arguments) [if] [in] [, options]

is fcn(arguments) which refers to a single egen function. Now meanI) is an egen function; anycountcount() would be a permitted egen function call if someone had written code for that, and the code were sitting on your machine, but it is perhaps more likely that you meant to write anycount(), which is an egen function.

But. as said, you can't combine calls to two or more egen functions in the same statement. Also, if anycount() is intended, then the option values() is required.

Someone else may be able to work out what you need here, as I can't follow the description.

[/CODE]