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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.