topic Re: Count previous 24 months from current row.(similar for each row). in SAS Programming

Count previous 24 months from current row.(similar for each row).

ramchinna24 — Wed, 19 Feb 2020 02:12:46 GMT

Hi,

I could do this in T sql. I am trying to do this in SAS. Can any one help me please?

I am trying to count principal relief flag(Y) from current row to previous 24 months. 
It is similar for each row.

data I have:

snapshotdate	id	code	PrincipalReliefFlag
20170831	111	1249	Y
20170930	111	1249	Y
20171031	111	1249	Y
20171130	111	1249	Y
20171231	111	1249	Y
20180131	111	1249	Y
20180228	111	1249	Y
20180331	111	1249	Y
20180430	111	1249	Y
20180531	111	1249	Y
20180630	111	1249	Y
20180731	111	1249	Y
20180831	111	1249	N
20180930	111	1249	Y
20181031	111	1249	Y
20181130	111	1249	Y
20181231	111	1249	Y
20190131	111	1249	Y
20190228	111	1249	Y
20190331	111	1249	Y
20190430	111	1249	Y
20190531	111	1249	Y
20190630	111	1249	Y
20190731	111	1249	Y
20190831	111	1249	N
20190930	111	1249	N
20191031	111	1249	N
20191130	111	1249	N
20191231	111	1249	N
20200131	111	1249	N



Data I need

snapshotdate	id	code	PrincipalReliefFlag	PRinlast124months
20170831	111	1249	Y	1
20170930	111	1249	Y	2
20171031	111	1249	Y	3
20171130	111	1249	Y	4
20171231	111	1249	Y	5
20180131	111	1249	Y	6
20180228	111	1249	Y	7
20180331	111	1249	Y	8
20180430	111	1249	Y	9
20180531	111	1249	Y	10
20180630	111	1249	Y	11
20180731	111	1249	Y	12
20180831	111	1249	N	12
20180930	111	1249	Y	13
20181031	111	1249	Y	14
20181130	111	1249	Y	15
20181231	111	1249	Y	16
20190131	111	1249	Y	17
20190228	111	1249	Y	18
20190331	111	1249	Y	19
20190430	111	1249	Y	20
20190531	111	1249	Y	21
20190630	111	1249	Y	22
20190731	111	1249	Y	23
20190831	111	1249	N	22
20190930	111	1249	N	21
20191031	111	1249	N	20
20191130	111	1249	N	19
20191231	111	1249	N	18
20200131	111	1249	N	17

Re: Count previous 24 months from current row.(similar for each row).

PaigeMiller — Tue, 18 Feb 2020 23:00:43 GMT

If you have PROC EXPAND (which is part of SAS/ETS), then it's pretty easy. Convert your Y and N to 1 and 0 respectively, then see this example, where you want MOVSUM instead of MOVAVE.

If you don't have PROC EXPAND, then here are links that show how to do it in a data step: https://blogs.sas.com/content/iml/2016/01/27/moving-average-in-sas.html

Re: Count previous 24 months from current row.(similar for each row).

ramchinna24 — Wed, 19 Feb 2020 01:28:42 GMT

data xx;
input snapshotdate id code PrincipalReliefFlag $;
cards;
20170831 111 1249 Y
20170930 111 1249 Y
20171031 111 1249 Y
20171130 111 1249 Y
20171231 111 1249 Y
20180131 111 1249 Y
20180228 111 1249 Y
20180331 111 1249 Y
20180430 111 1249 Y
20180531 111 1249 Y
20180630 111 1249 Y
20180731 111 1249 Y
20180831 111 1249 N
20180930 111 1249 Y
20181031 111 1249 Y
20181130 111 1249 Y
20181231 111 1249 Y
20190131 111 1249 Y
20190228 111 1249 Y
20190331 111 1249 Y
20190430 111 1249 Y
20190531 111 1249 Y
20190630 111 1249 Y
20190731 111 1249 Y
20190831 111 1249 N
20190930 111 1249 N
20191031 111 1249 N
20191130 111 1249 N
20191231 111 1249 N
20200131 111 1249 N
;
run;

data yy;
set xx;
snapshotdate1 = INPUT(PUT(snapshotdate,8.),YYMMDD8.);
FORMAT snapshotdate1 date9.;
if PrincipalReliefFlag='Y' then PRFLG=1;
else PRFLG=0;
drop snapshotdate;
run;

data zz;
set yy;
by code id snapshotdate1 ;
array vars(24) _temporary_;

if first.id then do;
call missing(of vars(*));
count=0;
end;
count+1;

vars(mod(count, 24)+1) = PRFLG; **can anyone explain this please?;

if _n_=1 then sums=PRFLG;
else if count>=24 then sums=sum(of vars(*));
else if count<24 then sums+PRFLG;

run;

I tried this. It is working But can you please explain what is happening here "vars(mod(count, 24)+1) = PRFLG"?

Re: Count previous 24 months from current row.(similar for each row).

PaigeMiller — Wed, 19 Feb 2020 13:32:02 GMT

@ramchinna24 wrote:

I tried this. It is working But can you please explain what is happening here "vars(mod(count, 24)+1) = PRFLG"?

Where did you get this code from? I don't see anything similar at the links provided.