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;
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;
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;
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.