Re: Vertical summation by condition

Cho8 · Posted 10-29-2020 09:58 AM

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 · Posted 10-29-2020 10:25 AM

@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 · Posted 10-30-2020 07:53 AM

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 · Posted 11-09-2020 08:49 AM

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

Ksharp · Posted 11-10-2020 06:50 AM

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

Cho8 · Posted 11-09-2020 09:16 AM

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 · Posted 10-29-2020 10:13 AM

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;