Mixing features from fiscal and calendar values

[Miguel A. Duran]

Hi, Statalisters.

I am using CALENDAR quarter values for a variable (income). For those cases where I have missing values, I want to replace the missing value with another value, which is obtained form the corresponding annual FISCAL value. Fiscal years/quarters do not always match calendar years/quarters.

The specific way in which I want to replace missing values is as follows: for a given CALENDAR quarter, if all the FISCAL quarters of the FISCAL year to which that CALENDAR quarter belongs have missing values, I would use the ANNUAL fiscal value divided by 4; if three fiscal quarters of the fiscal year to which the calendar quarter belongs have missing values, I would use (i) the annual fiscal value less the value of the fiscal quarter that does not have a missing value (ii) divided by 3; if two fiscal quarters of the fiscal year to which the calendar quarter belongs have missing values, I would use (i) the annual fiscal value less the values of the fiscal quarters that do not have missing values (ii) divided by 2; etc.

fiscal quarter	calendar quarter	fiscal year	annual income	quarter income
2005Q4	2006Q1	2005	85	.
2006Q1	2006Q2	2006	125	.
2006Q2	2006Q3	2006	125	.
2006Q3	2006Q4	2006	125	35
2006Q4	2007Q1	2006	125	.

Suppose I want to replace the missing values of the calendar quarters 2006Q2, 2006Q3 and 2007Q1. In the fiscal year to which this calendar quarter belongs (ie, the fiscal year 2006), the value of the fiscal quarter 2006Q3 is not missing. Therefore, the value of the calendar quarters 2006Q2, 2006Q3 and 2007Q1 would be (125 - 35)/3 = 30.

Can anyone please help me to find out a way to do this? Thank you very much in advance.

Miguel A.

[Roberto Ferrer]

Miguel,

I sense some details are missing in your question in ways I can't explain right now (time issues). But consider something like this to start with:

clear
set more off

*----- example data -----

input ///
str6(fquart cquart) fyear yearinc quartinc
2005Q4     2006Q1     2005     85     .
2006Q1     2006Q2     2006     125     .
2006Q2     2006Q3     2006     125     .
2006Q3     2006Q4     2006     125     35
2006Q4     2007Q1     2006     125     .
end

gen fquart2 = quarterly(fquart, "YQ")
gen cquart2 = quarterly(cquart, "YQ")

format %tq fquart2 cquart2
drop fquart cquart
rename *2 *
list

*----- what you want -----

bysort fyear: egen totquartinc = total(quartinc)
by fyear: egen missquartinc = total(missing(quartinc))

gen quartinc2 = quartinc
replace quartinc2 = (yearinc - totquartinc) / missquartinc if missing(quartinc)

list, abbreviate(15)

I'm sure you'll need to adjust it in some way(s), but I hope it's enough to get you started.

(Please note our preference for full real names as user logins. You can have it changed asking administrators with the "contact us" button.)

[Miguel A. Duran]

Thanks, Roberto. It seems to work. Best, M.A.