Data Step Consecutive Sum

[atnk]

Hi, In the data set below, I need to sum the last 3 records of (target) to create the var (sumlast3). The var hit is simply if sumlast3 equals 1. I used the lag to create sumlast3 but it is not working. sumlast3=lag(target)+lag2(target)+lag3(target);

obs	target
a	1
a	0
a	1
a	1
a	1
a	1
b	0
b	1
b	1
b	1
b	1

Want:

obs	target	sumlast3	hit
a	1
a	0
a	1	2	0
a	1	2	0
a	1	3	1
a	1	3	1
b	0
b	1
b	1	2	0
b	1	3	1
b	1	3	1

Thanks. 

[pink_poodle]

The solution could use a method from this post:
https://communities.sas.com/t5/SAS-Programming/How-to-find-flag-the-second-to-last-observation-by-ID...

[mkeintz · Accepted solution]

Your WANT dataset seems the show that you want

sumlast3=target+lag(target)+lag2(target)

which includes the current observation and the prior 2 obs.

But you report using 

sumlast3=lag(target)+lag2(target)+lag3(target);

which excludes the current observation and includes the prior three.

Also, since you want to reset sumlast3 to missing for the first two observations for any given OBS value, you'll probably want something like:

sumlast3=sum(target,lag(target),lag2(target));
if lag2(obs)^=obs then sumlast3=.;

However, you haven't explained the rules for calculating HIT.

I use the SUM function instead of the "+" operators to avoid error messages for the first two observations of the dataset  (the lagged values would be missing).

---

[atnk]

Thanks, it’s a typo, the hit equals 1 when sumlast3 equals 3. The lag with the sum function works well.

[Kurt_Bremser]

In your want data, sumlast3 never equals 1, so hit should never be set to 1.

---

@atnk wrote:

Hi, In the data set below, I need to sum the last 3 records of (target) to create the var (sumlast3). The var hit is simply if sumlast3 equals 1. I used the lag to create sumlast3 but it is not working. sumlast3=lag(target)+lag2(target)+lag3(target);

obs	target
a	1
a	0
a	1
a	1
a	1
a	1
b	0
b	1
b	1
b	1
b	1

Want:

obs	target	sumlast3	hit
a	1
a	0
a	1	2	0
a	1	2	0
a	1	3	1
a	1	3	1
b	0
b	1
b	1	2	0
b	1	3	1
b	1	3	1

Thanks. 

---

[Ksharp]

data have;
infile cards expandtabs truncover;
input obs $ target;
cards;
a	1
a	0
a	1
a	1
a	1
a	1
b	0
b	1
b	1
b	1
b	1
;

data want;
 set have;
 target1=lag(target);target2=lag2(target);
 if obs=lag(obs) and obs=lag2(obs) then do;
  sumlast3=sum(target,target1,target2);
  hit=ifn(sumlast3=3,1,0);
 end;
 drop target1 target2;
run;

[atnk]

Thanks, this solution also works.