topic Re: How to have two different sequence ID's within the same grouping of data in SAS Programming

How to have two different sequence ID's within the same grouping of data

joshCRF — Sun, 07 Apr 2024 23:10:22 GMT

I'm wanting to create a sequencing variable that can be used to distinguish two different entities within the same grouping (if that makes sense!)

Below is a screenshot of the data I'm using (will try upload some actual datalines when it works) and the column highlighted in yellow is how I would like the sequencing variable to be grouped. So the data is currently grouped by SUBJECT, TERM and START. I'd like the sequencing variable to indicate that there are effectively two different instances within the same group of SUBJECT and TERM.

CFLG indicates where these terms are linked, and as the data below shows there is a linking for the first 3 records, but then doesn't link again till the last two. So would like to indicate that there are two instances within this with the use of the NEW SEQUENCE VAR that I would like to create.

The dataset I'm using is currently as follows (understand this is very barebones at the moment):

data example2;
set example;
by subject term start;

if first.subject then newseq=0;

run;

I've tried different variations of if first.cflg or first.term etc, just to absolutely no avail. It's probably the simplest thing I'm missing.

Any support would be greatly appreciated!

Re: How to have two different sequence ID's within the same grouping of data

Patrick — Mon, 08 Apr 2024 00:57:05 GMT

Based on your picture something like below should work (not tested).

data example2;
  set example;
  by subject term start;

  lag_cflg=lag(cflg);

  if first.term then r_newseq=cflg;
  else if cflg=1 and missing(lag(cflg)) then r_newseq+1;

  if cflg=1 then newseq=r_newseq;

  drop lag_cflg r_newseq;
run;

Re: How to have two different sequence ID's within the same grouping of data

mkeintz — Mon, 08 Apr 2024 01:10:42 GMT

It appears to me that observations come in clusters of CFLG=1 and CFLG=.. For each cluster with CFLG=1 you want a constant NEW_SEQ_VAR value for every obs in the cluster. You want the first such cluster to have NEW_SEQ_VAR=1, the second to have NEW_SEQ_VAR=2, etc.

And apparently, with each new SUBJECT, you want to reset the first CLFG=1 group to restart with NEW_SEQ_VAR=1.

If that's the case, then the BY statement needs only SUBJECT and CFLG, as below (untested in the absence of a working DATA step with sample data):

data want (drop=_:);
  set have;
  by subject cflg notsorted;
  if first.subject then _cflg_group=0;
  if first.cflg=1 and cflg=1 then _cflg_group+1;
  if cflg=1 then new_seq_var=_cflg_group;
run;

The data may be sorted by TERM and START within SUBJECT, but including TERM and START in the BY statement would be problematic.

Re: How to have two different sequence ID's within the same grouping of data

Ksharp — Mon, 08 Apr 2024 02:00:25 GMT

/*That would be better if you post some data by SAS code.*/
data have;
input subject id cflg;
cards;
1 1 1
1 2 1
1 3 1
1 4 .
1 5 .
1 6 .
1 7 .
1 8 .
1 9 .
1 10 .
1 11 1
1 12 1
2 1 1
2 2 1
2 3 1
2 4 .
2 5 .
;
data want(drop=count);
 set have;
 by subject;
 if first.subject then count=0;
 if first.subject or (cflg=1 and missing(lag(cflg)) ) then count+1;
 if cflg=1 then want=count;
run;