Vertical summation by condition

[Cho8]

---

@Ksharp wrote:

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;

---

i dnt have SAS IML.. Need solution in Datastep or Macro

[Cho8]

---

@Cho8 wrote:

---

@Ksharp wrote:

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;

---

i dnt have SAS IML.. Need solution in Datastep or Macro

---

i didnot get the logic of array part..can you just explain it..

[Ksharp]

array x{*} _:; /*Refer to variables _201201 _201202 _201203 ...........*/
count=0; /* how many obs should be used for MEAN*/
do i=1 to 13; /*Rolling windows */
n=_n_+i; /* when d1(first obs) start with 1+1=2 ,then 1+2=3 1+3=4 ......
when d2(second obs) start with 2+1=3 ,then 2+2=4 2+3=5 ....*/

if n>dim(x) then leave; /*If the length of windows is longer than the number of obs,
then don't run the following code, and enter the next loop*/

count+1; /*how many obs have been used for calculated AVG*/
sum=sum(sum,x{n}); /*calculated SUM of 201202 - 201302 for d1 */
end;
_avg=sum/count; /*calculated avg/mean */

[Cho8]

If missing value, i want to next value.. i.e count start from non-missing value

[Ksharp]

There is no missing value only 0 .
And Due to it is old post, plz start a new session/topic .

[Cho8]

X	Y0	Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9
201201	1%	0%	1%	0%	0%	(0%)	.	.	.	.
201202	3%	(1%)	2%	1%	0%	0%	1%	.	.	.
201203	1%	0%	1%	0%	(0%)	0%	0%	0%	.	.
201204	2%	1%	0%	1%	2%	0%	(0%)	(0%)	1%	.
201205	1%	0%	0%	2%	(0%)	0%	1%	0%	3%	1%
201206	0%	1%	1%	2%	0%	(0%)	0%	(0%)	1%	3%
201207	1%	2%	(0%)	(0%)	0%	0%	3%	0%	2%	(0%)
201208	1%	(0%)	0%	1%	1%	0%	2%	2%	(0%)	1%
201209	1%	1%	(0%)	3%	0%	2%	1%	(0%)	2%	3%
201210	2%	(0%)	(0%)	1%	4%	3%	2%	1%	4%	4%
201211	1%	2%	1%	(0%)	0%	(0%)	0%	3%	0%	0%
201212	2%	0%	1%	(0%)	1%	6%	0%	2%	3%	(0%)
201301	2%	2%	0%	(0%)	0%	0%	0%	(0%)	2%	(0%)
201302	0%	0%	1%	0%	0%	1%	(0%)	1%	5%	(1%)
201303	1%	1%	0%	1%	0%	1%	0%	1%	(0%)	1%
201304	0%	4%	1%	1%	1%	3%	2%	4%	(1%)	1%
AVG	0.012921	0.004853	0.005452	0.008107	0.006915	0.009627	0.008207	0.007293	0.018898	0.011633

[Ksharp]

Here is IML code , if you like it.

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 iml;
use have(keep=d:) nobs nobs;
read all var _num_ into x[c=vname] ;
close;
avg=j(1,ncol(x),.);
do i=1 to ncol(x);
  l=i+1;
  u=(i+13)><nobs;
  avg[i]=x[l:u,i][:];
end;
create avg from avg[c=vname];
append from avg;
close;
quit;

data want;
 set have avg ;
run;