I have the following data and would like to add a flag to each row if a condition is met in the previous row.
In the following data, I want a flag=1 if Cntr=S and only if the next row is FE followed by BC/ABC. I don't want 2/8/2019 observation for 101 and no data for 102 as there is no BC/ABC after FE.
Have:
id Date Evt Cntr
101 2/2/2019 FE
101 2/3/2019 BC S
101 2/4/2019 FE
101 2/5/2019 BC
101 2/6/2019 FE
101 2/7/2019 ABC
101 2/8/2019 FE
102 2/2/2019 FE
Want:
id Date Evt Cntr flag
101 2/2/2019 FE
101 2/3/2019 BC S
101 2/4/2019 FE 1
101 2/5/2019 BC 1
101 2/6/2019 FE 1
101 2/7/2019 ABC 1
101 2/8/2019 FE
102 2/2/2019 FE
I tried using lag
and retain
functions to solve this problem but did not get what I wanted. Please help !!
This is another case where DOW processing can compute the flagging state of a row.
Arrays can be used to track values in the group. The arrays simplify computing the flagging of multiple regions after the S
. Choose an array size greater than the largest expected group size.
data have;
infile datalines missover;
attrib
id format=4.
date informat=mmddyy10. format=mmddyy10.
evt length=$3
cntr length=$1
;
input
id Date Evt Cntr; datalines;
101 2/2/2019 FE
101 2/3/2019 BC S
101 2/4/2019 FE
101 2/5/2019 BC
101 2/6/2019 FE
101 2/7/2019 ABC
101 2/8/2019 FE
102 2/2/2019 FE
;
data want;
array evts[-1:1000] $3 _temporary_ ;
array flags[1000] $1 _temporary_;
call missing(of evts[*]);
call missing(of flags[*]);
do _n_ = 1 to dim(flags) until (last.id);
set have;
by id;
evts[_n_] = evt;
if cntr='S' then _s_index = _n_;
if 0 < _s_index < _n_ - 1 then
if evt in ('BC', 'ABC') then
if evts[_n_-1] = 'FE' then
do ;
flags[_n_] = '1';
flags[_n_-1] = '1';
end;
end;
if not last.id then do;
put 'ERROR: ' id= 'group size larger than array size';
stop;
end;
* apply flag value computed for each row of the group;
do _n_ = 1 to _n_;
set have;
flag = flags[_n_];
output;
end;
drop _:;
run;