topic how to create a group id in SAS Programming

how to create a group id

stataq — Thu, 11 Jan 2024 15:14:38 GMT

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 .

;

Re: how to create a group id

PaigeMiller — Thu, 11 Jan 2024 15:55:23 GMT

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;

Re: how to create a group id

Quentin — Thu, 11 Jan 2024 16:17:06 GMT

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

Re: how to create a group id

Quentin — Thu, 11 Jan 2024 16:26:05 GMT

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 ;

Re: how to create a group id

ballardw — Thu, 11 Jan 2024 16:41:07 GMT

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