topic Re: Keep only Consecutuive flags in consecutive cycles in SAS Programming

Keep only Consecutuive flags in consecutive cycles

Paakay — Fri, 14 May 2021 10:58:00 GMT

I have ID's with multiple flags in cycles however some id's flags are not consecutive. For example ID A has two flags in cycle 1 but none in cycle 2,3,4 but then has another flag in cycle 5. I want to keep only the flags in cycle 1 and delete flags in cycle 5 since there is a break in cycle at 2. Another example is ID B has flags in cycles 1, 2,3 but not in cycle 4 however has flags in cycles 5 and 6. Since there is a break in cycle at 4, I want to keep the consecutive flags in cycles 1,2,3 and delete cycles 5 and 6. Can someone help me with this. Thank you. Sample data is below

DATA chk;
   INPUT  ID $  Flag Cycle ;
CARDS;
A 1 1
A 1 1
A 1 5
A 1 5
B 1 1
B 1 2
B 1 3
B 1 5
B 1 6
C 1 1
C 1 1
C 1 2
C 1 4
C 1 5
;
RUN;

Output Data should look like this

Data chk2;
  INPUT  ID $  Flag Cycle ;
CARDS;
A 1 1
A 1 1
B 1 1
B 1 2
B 1 3
C 1 1
C 1 1
C 1 2
;
RUN;

Re: Keep only Consecutuive flags in consecutive cycles

ErikLund_Jensen — Fri, 14 May 2021 11:32:50 GMT

Hi @Paakay

One way to solve the problem is by using the lag function.

data want (drop=old_cycle outkey);
	set chk;
	by ID;
	retain outkey;
	old_Cycle = lag(Cycle);
	if first.ID then outkey = 1;
	else if Cycle > old_cycle + 1 then outkey = 0;
	if outkey = 1 then output;
run;

Re: Keep only Consecutuive flags in consecutive cycles

Ksharp — Fri, 14 May 2021 12:30:01 GMT

DATA chk;
   INPUT  ID $  Flag Cycle ;
CARDS;
A 1 1
A 1 1
A 1 5
A 1 5
B 1 1
B 1 2
B 1 3
B 1 5
B 1 6
C 1 1
C 1 1
C 1 2
C 1 4
C 1 5
;
RUN;

data want;
 set chk;
 by id;
 if first.id then group=0;
 if first.id or dif(cycle)>1 then group+1;
 if group=1;
run;

Re: Keep only Consecutuive flags in consecutive cycles

Paakay — Tue, 01 Jun 2021 15:38:28 GMT

Thanks Eriklund. That works but there was problem . What if I want to flag ID's with consecutive flags as 1 and those without consecutive flags as 0. For example ID 1 is consecutive because it starts from cycle 1 to cycle 4 so I create a flag for ID 1 as 1. However for ID's with no consecutive cycle numbers like ID 2 (its starts from cycle 2 to cycle 5, missing cycle 1) and ID 3 also starts from cycle 8 missing cycles 1-7, I flags these ID's as 0. Is there a way to go about this. Thanks you.

Data have;
 input ID $1. Cycle;
Datalines;
1    1
1    1
1    2
1    3
1    4
2   3
2   4
2   5
3   8
3   8
;
run;

Re: Keep only Consecutuive flags in consecutive cycles

Paakay — Tue, 01 Jun 2021 15:40:40 GMT

Thanks Ksharp. That works but there was problem . What if I want to flag ID's with consecutive flags as 1 and those without consecutive flags as 0. For example ID 1 is consecutive because it starts from cycle 1 to cycle 4 so I create a flag for ID 1 as 1. However for ID's with no consecutive cycle numbers like ID 2 (its starts from cycle 2 to cycle 5, missing cycle 1) and ID 3 also starts from cycle 8 missing cycles 1-7, I flags these ID's as 0. Is there a way to go about this. Thanks you.

Data have;
 input ID $1. Cycle;
Datalines;
1    1
1    1
1    2
1    3
1    4
2   3
2   4
2   5
3   8
3   8
;
run;

Re: Keep only Consecutuive flags in consecutive cycles

Ksharp — Wed, 02 Jun 2021 11:50:41 GMT

OK. Firstly pick up the ID which have 1.

DATA chk;
infile cards expandtabs truncover;
   INPUT  ID $  Flag Cycle ;
CARDS;
1 1   1
1  1  1
1 1   2
1  1  3
1 1   4
2 1  3
2 1  4
2 1  5
3 1  8
3  1 8
;
RUN;
proc sql;
create table have as
select * from chk
 where id in (select id from chk where cycle=1)
  order by id,flag,cycle;
quit;

data want;
 set have;
 by id;
 if first.id then group=0;
 if first.id or dif(cycle)>1 then group+1;
 if group=1;
run;