I want to calculate a division which the numerator is intraday variable and the denominator is the aggregate of daily values of two days before and after that specific date and name. This new variable is called adjusted as shown in the table below;
* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 name int date double time byte(Intraday daily) double adjusted
"A" 19693 -1.8934218e+12 1 . .
"A" 19693 -1.89342e+12 2 3 .
"A" 19694 -1.8934182e+12 3 . .
"A" 19694 -1.8934164e+12 2 5 .
"A" 19695 -1.89342e+12 2 . .0952381
"A" 19695 -1.8934182e+12 1 . .04761905
"A" 19695 -1.8934164e+12 1 4 .04761905
"A" 19696 -1.8934146e+12 1 . .04347826
"A" 19696 -1.8934128e+12 3 . .13043478
"A" 19696 -1.893411e+12 3 7 .13043478
"A" 19699 -1.8934218e+12 1 . .05
"A" 19699 -1.89342e+12 1 2 .05
"A" 19700 -1.8934182e+12 2 . .11111111
"A" 19700 -1.8934164e+12 3 5 .16666667
"A" 19709 -1.8934146e+12 1 . .07692308
"A" 19709 -1.8934128e+12 1 2 .07692308
"A" 19710 -1.893411e+12 2 2 .
"A" 19720 -1.8934218e+12 1 . .
"A" 19720 -1.89342e+12 1 2 .
"B" 19700 -1.8934182e+12 2 . .
"B" 19700 -1.8934146e+12 2 . .
"B" 19700 -1.893411e+12 1 5 .
"B" 19706 -1.8934218e+12 1 . .
"B" 19706 -1.8934182e+12 1 2 .
"B" 19707 -1.8934128e+12 2 . .13333333
"B" 19707 -1.893411e+12 1 3 .06666667
"B" 19716 -1.8934218e+12 1 1 .08333333
"B" 19717 -1.89342e+12 2 . .125
"B" 19717 -1.8934182e+12 2 4 .125
"B" 19718 -1.8934128e+12 2 2 .125
"B" 19722 -1.8934218e+12 3 . .
"B" 19722 -1.89342e+12 2 . .
"B" 19722 -1.8934182e+12 1 6 .
"B" 19726 -1.8934164e+12 1 . .
"B" 19726 -1.8934146e+12 1 . .
"B" 19736 -1.8934128e+12 1 3 .
end
format %tdnn/dd/CCYY date
format %tcHH:MM:SS time
Let me give you an example. the value 0.0952381 at date 12/3/2013 in adjusted column is (2/(3+5+4+7+2)). The value 2 as numerator is intraday value at 12/3/2013 and the denominator is the sum of daily values for two days before and after plus daily of that specific day. For another instance, the adjusted value 0.04347826 at date 12/4/2013 is (1/(3+5+4+7+2)).
How can I create the variable adjusted in table 1?
Note that your times are ill-defined.
. di %tc -1.8934218e+12
31dec1899 09:30:00
That does not bite here. With some struggle to work out your definitions -- you are counting days with defined values, not days as such -- I get this code (using rangestat from SSC):
bysort name (date daily): gen newdate = sum(daily < .)
rangestat (count) daily (sum) daily , int(newdate -2 2) by(name)
gen wanted = Intraday / daily_sum
And this result (I see that I should have generated wanted as double):
bysort name : list date Intraday daily adjusted wanted daily_* if daily < ., sep(0) noobs
-----------------------------------------------------------------------------------
-> name = A
+----------------------------------------------------------------------------+
| date Intraday daily adjusted wanted daily_~t daily_~m |
|----------------------------------------------------------------------------|
| 12/1/2013 2 3 . .1666667 3 12 |
| 12/2/2013 2 5 . .1052632 4 19 |
| 12/3/2013 1 4 .04761905 .047619 5 21 |
| 12/4/2013 3 7 .13043478 .1304348 5 23 |
| 12/7/2013 1 2 .05 .05 5 20 |
| 12/8/2013 3 5 .16666667 .1666667 5 18 |
| 12/17/2013 1 2 .07692308 .0769231 5 13 |
| 12/18/2013 2 2 . .1818182 4 11 |
| 12/28/2013 1 2 . .1666667 3 6 |
+----------------------------------------------------------------------------+
-----------------------------------------------------------------------------------
-> name = B
+----------------------------------------------------------------------------+
| date Intraday daily adjusted wanted daily_~t daily_~m |
|----------------------------------------------------------------------------|
| 12/8/2013 1 5 . .1 3 10 |
| 12/14/2013 1 2 . .0909091 4 11 |
| 12/15/2013 1 3 .06666667 .0666667 5 15 |
| 12/24/2013 1 1 .08333333 .0833333 5 12 |
| 12/25/2013 2 4 .125 .125 5 16 |
| 12/26/2013 2 2 .125 .125 5 16 |
| 12/30/2013 1 6 . .0666667 4 15 |
| 1/13/2014 1 3 . .0909091 3 11 |
+----------------------------------------------------------------------------+