Flag first record if there 5 consecutive same values in by group

[chinna0369]

Hi,

I have provided the data below. If there are five consecutive 2's in aval after sorting by usubjid parcat1 avisit paramcd then we need to flag first record from that group as Y. Can you please help me with this logic.

data have;
input avisit $1-9	parcat1 $10-16	USUBJID	$17-25 paramcd $26 aval 28;
datalines;
Baseline	SOCIAL	10007001 A	2
Baseline	SOCIAL	10007001 B 0
Baseline	SOCIAL	10007001 C 2
Baseline	SOCIAL	10007001 D	2
Baseline	SOCIAL	10007001 E	2
Baseline	SOCIAL	10007001 F	2
Baseline	SOCIAL	10007001 G	2
Baseline	SOCIAL	10007001 H	2
Baseline	SOCIAL	10007001 I	0
Baseline	SOCIAL	10007001 J	1
Baseline	SOCIAL	10007001 K	1
Baseline	SOCIAL	10007001 L	2
Baseline	SOCIAL	10007001 M	1
Baseline	SOCIAL	10007001 N	1
Baseline	SOCIAL	10007001 O	1
Baseline	SOCIAL	10007001 P	1
Baseline	SOCIAL	10007001 Q	0
Baseline	SOCIAL	10007001 R	0
Baseline	SOCIAL	10007001 S	0
;
run;

data want;
input avisit $1-9	parcat1 $10-16	USUBJID	$17-25 paramcd $26 aval 28 flag $30;
datalines;
Baseline	SOCIAL	10007001 A	2 
Baseline	SOCIAL	10007001 B 0 
Baseline	SOCIAL	10007001 C 2 Y
Baseline	SOCIAL	10007001 D	2 
Baseline	SOCIAL	10007001 E	2
Baseline	SOCIAL	10007001 F	2
Baseline	SOCIAL	10007001 G	2
Baseline	SOCIAL	10007001 H	2
Baseline	SOCIAL	10007001 I	0
Baseline	SOCIAL	10007001 J	1
Baseline	SOCIAL	10007001 K	1
Baseline	SOCIAL	10007001 L	2
Baseline	SOCIAL	10007001 M	1
Baseline	SOCIAL	10007001 N	1
Baseline	SOCIAL	10007001 O	1
Baseline	SOCIAL	10007001 P	1
Baseline	SOCIAL	10007001 Q	0
Baseline	SOCIAL	10007001 R	0
Baseline	SOCIAL	10007001 S	0
;
run;

Thanks in advance!

[Tom · Accepted solution]

I assume you mean you want to sort by PARAMCD but analyze by AVAL.  If so you need to use the NOTSORTED keyword on the BY statement.

You should be able to just use two DO loops.  One to count and the second to re-read and create the new FLAG variable.

data have;
  input avisit :$9. parcat1 $ USUBJID $ paramcd $ aval ;
datalines;
Baseline SOCIAL 10007001 A 2
Baseline SOCIAL 10007001 B 0
Baseline SOCIAL 10007001 C 2
Baseline SOCIAL 10007001 D 2
Baseline SOCIAL 10007001 E 2
Baseline SOCIAL 10007001 F 2
Baseline SOCIAL 10007001 G 2
Baseline SOCIAL 10007001 H 2
Baseline SOCIAL 10007001 I 0
Baseline SOCIAL 10007001 J 1
Baseline SOCIAL 10007001 K 1
Baseline SOCIAL 10007001 L 2
Baseline SOCIAL 10007001 M 1
Baseline SOCIAL 10007001 N 1
Baseline SOCIAL 10007001 O 1
Baseline SOCIAL 10007001 P 1
Baseline SOCIAL 10007001 Q 0
Baseline SOCIAL 10007001 R 0
Baseline SOCIAL 10007001 S 0
;

data want ;
  do n=1 by 1 until(last.aval);
     set have;
     by avisit parcat1 USUBJID aval notsorted;
  end;
  do until(last.aval);
     set have;
     by avisit parcat1 USUBJID aval notsorted;
     if first.aval and n>4 and aval=2 then flag='Y';
     else flag='N';
     output;
  end;
run;

Results

Obs    n     avisit     parcat1    USUBJID     paramcd    aval    flag

  1    1    Baseline    SOCIAL     10007001       A         2      N
  2    1    Baseline    SOCIAL     10007001       B         0      N
  3    6    Baseline    SOCIAL     10007001       C         2      Y
  4    6    Baseline    SOCIAL     10007001       D         2      N
  5    6    Baseline    SOCIAL     10007001       E         2      N
  6    6    Baseline    SOCIAL     10007001       F         2      N
  7    6    Baseline    SOCIAL     10007001       G         2      N
  8    6    Baseline    SOCIAL     10007001       H         2      N
  9    1    Baseline    SOCIAL     10007001       I         0      N
 10    2    Baseline    SOCIAL     10007001       J         1      N
 11    2    Baseline    SOCIAL     10007001       K         1      N
 12    1    Baseline    SOCIAL     10007001       L         2      N
 13    4    Baseline    SOCIAL     10007001       M         1      N
 14    4    Baseline    SOCIAL     10007001       N         1      N
 15    4    Baseline    SOCIAL     10007001       O         1      N
 16    4    Baseline    SOCIAL     10007001       P         1      N
 17    3    Baseline    SOCIAL     10007001       Q         0      N
 18    3    Baseline    SOCIAL     10007001       R         0      N
 19    3    Baseline    SOCIAL     10007001       S         0      N

[chinna0369]

Thanks for your reply! I have one more situtation, if we have multiple consecutive 5 2's in a same group of parcat1 and avisit and usubjid then we need to flag only first record instead of flagging every first record in each consecutive group. How can we do that, I can provide example data if I am confusing.

[chinna0369]

Hi @Tom , thanks for your reply! what if we need to check if there consecutive paramcd is there or not? I mean we need to flag if there are consecutive paramcd's without missing.

Thanks,

Adithya