Hello,
I have a have dataset, I want to create a group seq based on:
if id is the same;
if yn=1 and seq are connected to each other, then they belong to same group;
How can I create new_seq?
sample data:
data have ;
input id seq yn ;
cards;
1 1 .
1 2 .
1 3 1
1 4 1
1 5 .
1 6 .
1 7 .
1 8 1
1 9 1
1 10 1
1 11 .
1 12 .
1 13 1
1 14 1
1 15 .
1 16 1
1 17 1
2 1 1
2 2 .
2 3 1
2 4 1
2 5 .
2 6 .
2 7 1
2 8 1
2 9 .
;
If you can trust that your data are sorted by ID and seq, then you can can use by-group processing to detect the start of each group with yn=1, and increment a counter, e.g.:
data have ;
input id seq yn ;
cards;
1 1 .
1 2 .
1 3 1
1 4 1
1 5 .
1 6 .
1 7 .
1 8 1
1 9 1
1 10 1
1 11 .
1 12 .
1 13 1
1 14 1
1 15 .
1 16 1
1 17 1
2 1 1
2 2 .
2 3 1
2 4 1
2 5 .
2 6 .
2 7 1
2 8 1
2 9 .
;
data want ;
set have ;
by id yn notsorted ;
if first.id then _seqnum=0 ;
if first.yn and yn=1 then _seqnum++1 ;
if yn=1 then new_seq=_seqnum ;
* drop _: ;
run ;
data want;
set have;
by id seq;
prev_yn=lag(yn);
prev_id=lag(id);
if first.id then new_seq=.;
if id=prev_id and yn=1 and prev_yn=. then new_seq+1;
if yn=. then new_seq_plus_missing=.;
else new_seq_plus_missing=new_seq;
drop prev:;
run;
Hi @PaigeMiller ,
Looks like that misses the first record for ID2, which has yn=1 so is a new seq. Maybe replace
if first.id then new_seq=.;
with
if first.id then new_seq=(yn=1);
?
If you can trust that your data are sorted by ID and seq, then you can can use by-group processing to detect the start of each group with yn=1, and increment a counter, e.g.:
data have ;
input id seq yn ;
cards;
1 1 .
1 2 .
1 3 1
1 4 1
1 5 .
1 6 .
1 7 .
1 8 1
1 9 1
1 10 1
1 11 .
1 12 .
1 13 1
1 14 1
1 15 .
1 16 1
1 17 1
2 1 1
2 2 .
2 3 1
2 4 1
2 5 .
2 6 .
2 7 1
2 8 1
2 9 .
;
data want ;
set have ;
by id yn notsorted ;
if first.id then _seqnum=0 ;
if first.yn and yn=1 then _seqnum++1 ;
if yn=1 then new_seq=_seqnum ;
* drop _: ;
run ;
"Connected" should be defined. If you mean the variable Seq appears in sequential numeric order then say so.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.