Moving count using sql

[hhchenfx]

Hi,

My code below help to count the number within 3 prior date.

How can I include the n=_N_ in the sql?

Thanks,

HHC

data have;
input Year_Month State $ Value;
datalines;
201401 CA 1
201402 CA .
201403 CA 80
201404 CA 5
201405 CA 8
201401 TX 1
201402 TX 4
201403 TX .
201404 TX 35
201405 TX 10
;
data have; set have;
n=_N_;run;

proc sql;
create table want
as select a.*, count(b.value) as total from 
have as a left join have as b
on a.state=b.state and b.n<=a.n <=b.n+3
group by 1,2,3,4
order by a.state, a.n
;quit;

[PaigeMiller · Accepted solution]

SQL is a poor choice for moving counts, although I'm sure it can be done.

PROC EXPAND is the easiest method

data have1;
    set have;
    if not missing(value) then count=1;
    else count=0;
run;

proc expand data=have1 out=want;
 	by state; 
	convert count=moving_count/transformin=(movsum 3);
run;

[Ksharp]

data have;
input Year_Month State $ Value;
datalines;
201401 CA 1
201402 CA .
201403 CA 80
201404 CA 5
201405 CA 8
201401 TX 1
201402 TX 4
201403 TX .
201404 TX 35
201405 TX 10
;

proc sql;
create table want
as select a.*, 
(select count(b.value) from have as b where b.state=a.state and 
          mdy(mod(b.Year_Month,100),1,int(b.Year_Month/100)) 
 between  intnx('month',mdy(mod(a.Year_Month,100),1,int(a.Year_Month/100)) ,-3)
 and   mdy(mod(a.Year_Month,100),1,int(a.Year_Month/100)) 
 ) as total 
from
have as a 
;quit;