I have a table with a column called as flag with values (1,2,3,4). I want to create column called as Category based on certain conditions: 1) if flag 1, 2, 3, 4 appears consecutively for the first time will have their category as c1, c2, c3 and c4. 2) if consecutive flag=3 appears after 1), then the category will change to c5 - This condition can be accepted 2 times 3) if consecutive flag=4 appears after 2), then the category will change to c5 - This condition can be accepted 2 times 4) After 3), if flag=3 appears then category will be c7 5) After 5) if flag=4 appears then category will be c8 So for e.g. data have;
input Id YearMonth Flag;
datalines;
1 200101 1
1 200102 1
1 200103 2
1 200104 3
1 200105 3
1 200106 4
1 200107 4
1 200108 3
1 200109 4
1 200110 4
1 200111 3
1 200112 4
1 200201 3
1 200202 4
;
run; data want;
_n_=0;
do until(last.id);
do until(last.flag);
set have;
by id flag notsorted;
if first.id and first.flag=2 then _n_=2;
else if first.flag and _n_<4 then _n_=sum(_n_,1);
if _n_=4 then category=ifn(flag,4,3);
else category=_n_;
output;
end;
end;
run; I have tried using case statement and comparing each YearMonth to the previous YearMonth. It works but repeats the category. So in 11th row and 13th row, it gives c3 instead of c5 and c7. I think I have to use counter logic and group by but not sure how it will work.
... View more
