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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: