how to add sequence number to a group of specific consecutive records?

user_2993610 0
Viewed 1199

I do have a tricky one:

Here is my example data. It is sorted by a date variable (which is not included here). I want to calculate a a new variable, called seq_no which creates a grouping sequence number for each consecutive flagged records. The seq_no variable should look like in the example and I want to calculate it with SAS or SQL.

ID flag seq_no
1   Y      1
1
1   Y      2
1   Y      2
2
2   Y      1
2   Y      1
2
2   Y      2
3   Y      1
3   Y      1
3

Thanks a lot in advance! Stephan

sas sql
5 Answers

Unlike SAS datasets, SQL tables represent unordered sets. The following assumes that you have a column that specifies the ordering.

You can count the number of empty records before each "Y" and use that to assign a unique value:

proc sql;
    select t.*,
           (select count(*)
            from t t2
            where t2.id = t.id and t2.flag is null and t2.ordcol <= t.ordcol
           ) grp_id
    from t;

A "real" database would have more substantial functionality -- in particular window functions -- that would facilitate this effort.

user_11440350

As you said It is sorted by a date variable (which is not included here), this is an approach using Windowed Aggregates, which most DBMSes support:

with cte as 
 ( select id, flag, datecol,
      -- assign a new value whenever there's a NULL flag 
      sum(case when flag is null then 1 else 0 end)
      over (partition by id
            order by datecol
            rows unbounded preceding) as grp
   from tab
 )
select id, flag, datecol, grp,
   case when flag is not null
   then dense_rank() -- assign a sequence to each group of 'Y'
        over (partition by id
              order by grp)
   end
from cte

If you don't care if the sequence starts at 0 or 1 you can even simplify it to

select id, flag, datecol,
   case when flag is not null
   then sum(case when flag is null then 1 else 0 end)
        over (partition by id
              order by datecol
              rows unbounded preceding)
   end as grp2
from tab
user_25279050

SQL is not a good tool for this since it is designed for set operations, not sequential processing. (There are workarounds and advanced functions in other SQL implementations that can help, see the other answers.)

But in a data step it is simple with a retained variable.

data want;
  set have;
  by id ;  
  if first.id then seq_no=0;
  seq_no+(flag='Y');
run;

One wrinkle in your request is that you don't want the count on the records where the flag is not Y. That could be easily done by using a separate variable to retain the count than you use as your "seq_no" variable.

data want;
  set have;
  by id ;  
  if first.id then cumm_seq_no=0;
  cumm_seq_no+(flag='Y');
  if flag='Y' then seq_no=cumm_seq_no;
run;
user_49655490

Here is a base SAS method.

proc sort data=have; 
   by id date;

data want (drop=prev_seq);
   set have;
   by id;
   retain prev_seq;
   if first.id then prev_seq = .;
   if flag = 'Y' then do;
      prev_seq + 1;  
      seq_no = prev_seq;
   end;
run;
user_43858300

You want seq_no incremented at the start of a contiguous Y block of flag values. Use the NOTSORTED option of the BY statement to process contiguous blocks, when blocks are disordered or gapped.

Example:

data want;
  set have;
  by id flag NOTSORTED;

  if first.id then seq_num = 0;
  if first.flag and flag = 'Y' then seq_num+1;

  if flag='Y' 
    then seq_no = seq_num;
    else call missing(seq_no); 

  drop seq_num;
run;
user_12499620
Related