Aggregation Program

[Federico Nutarelli]

Hello everyone,

I have a "programming related problem" and would like to kindly ask for your help.
The issue is the following: I have quarterly data on sales names as: sales_"number of months taken by the nth trimester"_year, so for instance for the first trimester of 2004, we have "sales_03_2004", for the second "sales_06_2004", for the third "sales_09_2004" and for the last "sales_12_2004" and so on for every year until 2015. What I need to do is an yearly, 3-yearly and 4-yearly aggregation of those sales in such a way that changing a single parameter, the user can choose the desired aggregation level. I already have done 3 separate aggregations (yearly, 3-yearly and 4-yearly separately) but what I really need to do is to create a unified program in which the user can possibly choose the desired aggregation. For example something like: if I put the parameter equal to 4 I obtain a 4-yearly aggregation, if I put the parameter equal to 3 a 3-yearly an if I put the parameter equal to 1 an yearly aggregation. Is this possible?

Thank you in advance!!!

[Federico Nutarelli]

*GENERERAZIONE COUNTERS:
sort internationalproduct
forvalues i =  2004/2015 {
gen counter_`i' = 0
if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
gen counter_`i'_`=`i'+2' = 0
if `i'== 2004 | `i' == 2009 | `i'== 2012 {
gen counter_`i'_`=`i'+3' = 0 
}
}
}


*#delimit ;
capture program drop aggregation
program define aggregation
noi di " How do you want to aggregate? --> "  _request(answer)   /* noi is the abbreviation for noisily and di for di for display */
di "Aggregation =$answer "
if "‘answer’" == "yearly" {
forvalues i =  2004/2015 {
gen yearly_sales_`i' = 0
bysort internationalproduct: replace yearly_sales_`i' = sum_sales_03_`i' + sum_sales_06_`i'+sum_sales_09_`i' + sum_sales_12_`i'
 replace counter_`i'= counter_`i' + 1  if sum_sales_03_`i' + sum_sales_06_`i' == 0
 replace yearly_sales_`i' = 0 if counter_`i' > 0//
 }
} //
if "‘answer’" == "threeyearly" {
 foreach i in 2004 2007 2010  2013 { 
gen triennial_sales_`i'_`=`i'+2' = 0 
bysort internationalproduct: replace triennial_sales_`i'_`=`i'+2' = yearly_sales_`i' + yearly_sales_`=`i'+1'+yearly_sales_`=`i'+2'
 replace counter_`i'_`=`i'+2' = counter_`i'_`=`i'+2' + 1  if yearly_sales_`i' + yearly_sales_`=`i'+1' == 0
replace triennial_sales_`i'_`=`i'+2' = 0 if counter_`i'_`=`i'+2' > 0 //
}
}//
else{
foreach i in 2004 2009 2012 {
gen quadriennial_sales_`i'_`=`i'+3' = 0 
bysort internationalproduct: replace quadriennial_sales_`i'_`=`i'+3' = yearly_sales_`i' + yearly_sales_`=`i'+1'+yearly_sales_`=`i'+2'+yearly_sales_`=`i'+3'
 replace counter_`i'_`=`i'+3' = counter_`i'_`=`i'+3' + 1  if yearly_sales_`i' + yearly_sales_`=`i'+1'+ yearly_sales_`=`i'+2' == 0
replace quadriennial_sales_`i'_`=`i'+3' = 0 if counter_`i' > 0 //
 }
 }
 end

This is my attempt btw. It does not work...one of the errors is "program error: code follows on the same line as close brace"
Thanks again

[Nick Cox]

You need to reshape long and then aggregations will be easy. Your present data layout is not fit for purpose.

[Phil Bromiley]

While Nick's suggestion is absolutely correct, I suspect your error message comes from
}// Not having a space after } messes this up.

[Federico Nutarelli]

First of all many thanks for the feedback. So it seems that I have done progress since I post the "help request".
This is my code. I have not done the reshape and I was wondering whether it works as well. Otherwise I will follow your suggestions and review everything.

Many thanks again. Here is the code:

clear all
set more off
use "/Users/federiconutarelli 1/Desktop/Pharma_industries/Dataset_Pharma/US_PHARMA_FINAL copia.dta"

*GENERERAZIONE COUNTERS:
*sort internationalproduct
*forvalues i =  2004/2015 {
*gen counter_`i' = 0
*if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
*gen counter_`i'_`=`i'+2' = 0
*if `i'== 2004 | `i' == 2009 | `i'== 2012 {
*gen counter_`i'_`=`i'+3' = 0 
*}
*}
*}


*#delimit ;
capture program drop aggregation
program define aggregation
noi di " How do you want to aggregate? --> "  _request(answer)   /* noi is the abbreviation for noisily and di for di for display */
di "Aggregation =$answer "
if "${answer}" == "yearly" { 
forvalues i =  2004/2015 {
gen counter_`i' = 0
if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
gen counter_`i'_`=`i'+2' = 0
if `i'== 2004 | `i' == 2009 | `i'== 2012 {
gen counter_`i'_`=`i'+3' = 0 
}
}
gen yearly_sales_`i' = 0
bysort internationalproduct: replace yearly_sales_`i' = sum_sales_03_`i' + sum_sales_06_`i'+sum_sales_09_`i' + sum_sales_12_`i'
 replace counter_`i'= counter_`i' + 1  if sum_sales_03_`i' + sum_sales_06_`i' == 0
 replace yearly_sales_`i' = 0 if counter_`i' > 0
 }
forvalues i =  2004/2015 {
drop counter_`i' 
if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
drop counter_`i'_`=`i'+2'
if `i'== 2004 | `i' == 2009 | `i'== 2012 { 
drop counter_`i'_`=`i'+3' 
}
}
}
}
if "${answer}" == "threeyearly" {
forvalues i =  2004/2015 {
gen counter_`i' = 0
if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
gen counter_`i'_`=`i'+2' = 0
*if `i'== 2004 | `i' == 2009 | `i'== 2012 {
gen counter_`i'_`=`i'+3' = 0 
*}
}
gen yearly_sales_`i' = 0
bysort internationalproduct: replace yearly_sales_`i' = sum_sales_03_`i' + sum_sales_06_`i'+sum_sales_09_`i' + sum_sales_12_`i'
 replace counter_`i'= counter_`i' + 1  if sum_sales_03_`i' + sum_sales_06_`i' == 0
 replace yearly_sales_`i' = 0 if counter_`i' > 0
}
foreach i in 2004 2007 2010  2013 { 
gen triennial_sales_`i'_`=`i'+2' = 0 
bysort internationalproduct: replace triennial_sales_`i'_`=`i'+2' = yearly_sales_`i' + yearly_sales_`=`i'+1'+yearly_sales_`=`i'+2'
 replace counter_`i'_`=`i'+2' = counter_`i'_`=`i'+2' + 1  if yearly_sales_`i' + yearly_sales_`=`i'+1' == 0
replace triennial_sales_`i'_`=`i'+2' = 0 if counter_`i'_`=`i'+2' > 0 
}
forvalues i =  2004/2015 {
drop counter_`i' 
drop yearly_sales_`i'
if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
drop counter_`i'_`=`i'+2' 
*if `i'== 2004 | `i' == 2009 | `i'== 2012 {
drop counter_`i'_`=`i'+3' 
*}
}
}
}
if "${answer}" == "fouryearly"{
forvalues i =  2004/2015 {
gen counter_`i' = 0
*if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
gen counter_`i'_`=`i'+2' = 0
if `i'== 2004 | `i' == 2009 | `i'== 2012 {
gen counter_`i'_`=`i'+3' = 0 
*}
}
gen yearly_sales_`i' = 0
bysort internationalproduct: replace yearly_sales_`i' = sum_sales_03_`i' + sum_sales_06_`i'+sum_sales_09_`i' + sum_sales_12_`i'
 replace counter_`i'= counter_`i' + 1  if sum_sales_03_`i' + sum_sales_06_`i' == 0
 replace yearly_sales_`i' = 0 if counter_`i' > 0
 }
foreach i in 2004 2009 2012 {
gen quadriennial_sales_`i'_`=`i'+3' = 0 
bysort internationalproduct: replace quadriennial_sales_`i'_`=`i'+3' = yearly_sales_`i' + yearly_sales_`=`i'+1'+yearly_sales_`=`i'+2'+yearly_sales_`=`i'+3'
 replace counter_`i'_`=`i'+3' = counter_`i'_`=`i'+3' + 1  if yearly_sales_`i' + yearly_sales_`=`i'+1'+ yearly_sales_`=`i'+2' == 0
replace quadriennial_sales_`i'_`=`i'+3' = 0 if counter_`i' > 0 
}
forvalues i =  2004/2015 {
drop counter_`i'
drop yearly_sales_`i' 
*if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{
drop counter_`i'_`=`i'+2'
if `i'== 2004 | `i' == 2009 | `i'== 2012 { 
drop counter_`i'_`=`i'+3' 
}
}
}
*}

 end

 
 
*CANCELLAZIONE COUNTERS:
*sort internationalproduct
*forvalues i =  2004/2015 {
*drop counter_`i' 
*if `i'== 2004 |`i' ==2007 | `i'== 2010 | `i'== 2013{ 
*drop counter_`i'_`=`i'+2' 
*if `i'== 2004 | `i' == 2009 | `i'== 2012 {
*drop counter_`i'_`=`i'+3' 
*}
*}
*}