topic Re: Data Step Consecutive Sum in SAS Programming

Data Step Consecutive Sum

atnk — Wed, 04 May 2022 16:44:38 GMT

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.

Re: Data Step Consecutive Sum

pink_poodle — Wed, 04 May 2022 22:05:48 GMT

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/td-p/749175

Re: Data Step Consecutive Sum

mkeintz — Wed, 04 May 2022 22:16:57 GMT

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).

Re: Data Step Consecutive Sum

Kurt_Bremser — Thu, 05 May 2022 05:07:47 GMT

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.

Re: Data Step Consecutive Sum

Ksharp — Thu, 05 May 2022 13:52:24 GMT

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;

Re: Data Step Consecutive Sum

atnk — Thu, 05 May 2022 18:55:38 GMT

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

Re: Data Step Consecutive Sum

atnk — Thu, 05 May 2022 18:57:24 GMT

Thanks, this solution also works.