Vertical summation by condition

[Cho8]

Y	d1	d2	d3	d4	d5	d6	d7	d8	d9
201201	0.01	0.00	0.01	0.00	0.00	0.00	0.01	0.01	0.01
201202	0.03	-0.01	0.02	0.01	0.00	0.00	0.01	0.00	0.04
201203	0.01	0.00	0.01	0.00	0.00	0.00	0.00	0.00	0.02
201204	0.02	0.01	0.00	0.01	0.02	0.00	0.00	0.00	0.01
201205	0.01	0.00	0.00	0.02	0.00	0.00	0.01	0.00	0.03
201206	0.00	0.01	0.01	0.02	0.00	0.00	0.00	0.00	0.01
201207	0.01	0.02	0.00	0.00	0.00	0.00	0.03	0.00	0.02
201208	0.01	0.00	0.00	0.01	0.01	0.00	0.02	0.02	0.00
201209	0.01	0.01	0.00	0.03	0.00	0.02	0.01	0.00	0.02
201210	0.02	0.00	0.00	0.01	0.04	0.03	0.02	0.01	0.04
201211	0.01	0.02	0.01	0.00	0.00	0.00	0.00	0.03	0.00
201212	0.02	0.00	0.01	0.00	0.01	0.06	0.00	0.02	0.03
201301	0.02	0.02	0.00	0.00	0.00	0.00	0.00	0.00	0.02
201302	0.00	0.00	0.01	0.00	0.00	0.01	0.00	0.01	0.05
201303	0.01	0.01	0.00	0.01	0.00	0.01	0.00	0.01	0.00
201304	0.00	0.04	0.01	0.01	0.01	0.03	0.02	0.04	-0.01

I need average for 12 observation for every row..

for instance.,

for D1..start at row2 to row 14 and average displayed at the bottom of row..

for D2..start at row3 to row15 and average displayed at bottom of row...

for D3 start at row4 to row16 and average at bottom of row...

for all the variables from D1 until D9..

[PaigeMiller]

I need average for 12 observation for every row.

Please clarify this. Do you really mean "I need average for 12 observation for every column."?

[Cho8]

its vertical summation..not by column

[PaigeMiller]

---

@Cho8 wrote:

its vertical summation..not by column

---

It sure seems you want to do this by column, but you say "NOT by column". Can you please explain?

[PeterClemmensen]

Do you really want the average to be displayed at the bottom of the row? Can you post exactly what you want the result to look like?

[Cho8]

for d1... average of 201202 until201302 at bottom..

for d2 ...average of 201203 until 201303 at bottom..

and so on..

it should be vertical sum..

[Cho8]

Y	d1	d2	d3	d4	d5	d6	d7	d8	d9
201201	0.01	0.00	0.01	0.00	0.00	0.00	0.01	0.01	0.01
201202	0.03	-0.01	0.02	0.01	0.00	0.00	0.01	0.00	0.04
201203	0.01	0.00	0.01	0.00	0.00	0.00	0.00	0.00	0.02
201204	0.02	0.01	0.00	0.01	0.02	0.00	0.00	0.00	0.01
201205	0.01	0.00	0.00	0.02	0.00	0.00	0.01	0.00	0.03
201206	0.00	0.01	0.01	0.02	0.00	0.00	0.00	0.00	0.01
201207	0.01	0.02	0.00	0.00	0.00	0.00	0.03	0.00	0.02
201208	0.01	0.00	0.00	0.01	0.01	0.00	0.02	0.02	0.00
201209	0.01	0.01	0.00	0.03	0.00	0.02	0.01	0.00	0.02
201210	0.02	0.00	0.00	0.01	0.04	0.03	0.02	0.01	0.04
201211	0.01	0.02	0.01	0.00	0.00	0.00	0.00	0.03	0.00
201212	0.02	0.00	0.01	0.00	0.01	0.06	0.00	0.02	0.03
201301	0.02	0.02	0.00	0.00	0.00	0.00	0.00	0.00	0.02
201302	0.00	0.00	0.01	0.00	0.00	0.01	0.00	0.01	0.05
201303	0.01	0.01	0.00	0.01	0.00	0.01	0.00	0.01	0.00
201304	0.00	0.04	0.01	0.01	0.01	0.03	0.02	0.04	-0.01
201305	0.01	0.00	0.00	0.00	0.00	0.00	0.00	0.01	0.00
201306	0.00	0.01	0.00	0.02	0.00	0.02	0.01	0.00	0.00
201307	0.01	0.01	0.04	0.01	0.01	0.01	0.00	0.01	0.02
201308	0.02	0.00	0.00	0.01	0.01	0.03	0.12	0.00	0.01
201309	0.01	0.00	0.01	0.02	0.03	0.03	0.00	-0.01	0.03
201310	0.01	0.00	0.00	0.01	0.00	0.01	0.01	0.01	0.00
avg	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.02

[Kurt_Bremser]

Use PROC REPORT:

data have;
input y $ d1-d9;
datalines;
201201  0.01  0.00  0.01  0.00  0.00  0.00  0.01  0.01  0.01
201202  0.03  -0.01 0.02  0.01  0.00  0.00  0.01  0.00  0.04
201203  0.01  0.00  0.01  0.00  0.00  0.00  0.00  0.00  0.02
201204  0.02  0.01  0.00  0.01  0.02  0.00  0.00  0.00  0.01
201205  0.01  0.00  0.00  0.02  0.00  0.00  0.01  0.00  0.03
201206  0.00  0.01  0.01  0.02  0.00  0.00  0.00  0.00  0.01
201207  0.01  0.02  0.00  0.00  0.00  0.00  0.03  0.00  0.02
201208  0.01  0.00  0.00  0.01  0.01  0.00  0.02  0.02  0.00
201209  0.01  0.01  0.00  0.03  0.00  0.02  0.01  0.00  0.02
201210  0.02  0.00  0.00  0.01  0.04  0.03  0.02  0.01  0.04
201211  0.01  0.02  0.01  0.00  0.00  0.00  0.00  0.03  0.00
201212  0.02  0.00  0.01  0.00  0.01  0.06  0.00  0.02  0.03
201301  0.02  0.02  0.00  0.00  0.00  0.00  0.00  0.00  0.02
201302  0.00  0.00  0.01  0.00  0.00  0.01  0.00  0.01  0.05
201303  0.01  0.01  0.00  0.01  0.00  0.01  0.00  0.01  0.00
201304  0.00  0.04  0.01  0.01  0.01  0.03  0.02  0.04  -0.01
;

proc report data=have;
column y d:;
define y / display;
define d: / analysis mean format=7.2;
rbreak after / summarize;
run;

[PeterClemmensen]

@Kurt_Bremser, I think this a bit too simple since the window of interest changes for each d1, d2 ... 

[Kurt_Bremser]

But it creates the expected output as displayed. I first suspected a need for a rolling window, but the OP seems to want one summary line for all numbers.

[Cho8]

for d1..average from 201202 to 201302..

for d2 average from 201203 to 201303..

for d3 average from 201204 to 201304..

and so on..

[PeterClemmensen]

Is this for reporting purposes or do you want a SAS data set like this?

[Cho8]

---

@PeterClemmensen wrote:

Is this for reporting purposes or do you want a SAS data set like this?

---

Need dataset like that..

[Cho8]

please check my post on how result should look like..

[Ksharp]

Do you have SAS/IML ? I would like to use IML code for this kind of question.

data have;
input y $ d1-d9;
datalines;
201201  0.01  0.00  0.01  0.00  0.00  0.00  0.01  0.01  0.01
201202  0.03  -0.01 0.02  0.01  0.00  0.00  0.01  0.00  0.04
201203  0.01  0.00  0.01  0.00  0.00  0.00  0.00  0.00  0.02
201204  0.02  0.01  0.00  0.01  0.02  0.00  0.00  0.00  0.01
201205  0.01  0.00  0.00  0.02  0.00  0.00  0.01  0.00  0.03
201206  0.00  0.01  0.01  0.02  0.00  0.00  0.00  0.00  0.01
201207  0.01  0.02  0.00  0.00  0.00  0.00  0.03  0.00  0.02
201208  0.01  0.00  0.00  0.01  0.01  0.00  0.02  0.02  0.00
201209  0.01  0.01  0.00  0.03  0.00  0.02  0.01  0.00  0.02
201210  0.02  0.00  0.00  0.01  0.04  0.03  0.02  0.01  0.04
201211  0.01  0.02  0.01  0.00  0.00  0.00  0.00  0.03  0.00
201212  0.02  0.00  0.01  0.00  0.01  0.06  0.00  0.02  0.03
201301  0.02  0.02  0.00  0.00  0.00  0.00  0.00  0.00  0.02
201302  0.00  0.00  0.01  0.00  0.00  0.01  0.00  0.01  0.05
201303  0.01  0.01  0.00  0.01  0.00  0.01  0.00  0.01  0.00
201304  0.00  0.04  0.01  0.01  0.01  0.03  0.02  0.04  -0.01
;

proc transpose data=have out=temp name=y prefix=_;
var d1-d9;
id y;
run;

data temp;
 set temp;
 array x{*} _:;
 count=0;
 do i=1 to 13;
  n=_n_+i;
  if n>dim(x) then leave;
  count+1;
  sum=sum(sum,x{n});
 end;
 _avg=sum/count;
 drop i n count sum;
run;

proc transpose data=temp out=want;
var _:;
id y;
run;