BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
stataq
Quartz | Level 8

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? 

 

stataq_0-1704985902761.png

 

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 .

;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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 ;

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
Quentin
Super User

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);

?

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Quentin
Super User

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 ;

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
ballardw
Super User

"Connected" should be defined. If you mean the variable Seq appears in sequential numeric order then say so.

 

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 909 views
  • 1 like
  • 4 in conversation