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