Two consecutive visit records, Flag first record

[Almighty]

Hello Gurus,

I am having hard time to flag at least two consecutive visit records per subject as one:  Ex: if ALTX3ULN is 1 for two/more consecutive visits, then first record should be FLAG as 'Y' for that subject.  please see below data have and out needed (also attached as excel files).

DATA HAVE

SUBJNO	VISMON	SAMPDT	TESTCODE	ALTX3ULN
1	1	30NOV2005:00:00:00	SGPT_ALT	0
1	4	01MAR2006:00:00:00	SGPT_ALT	0
1	8	28JUN2006:00:00:00	SGPT_ALT	0
1	12	25OCT2006:00:00:00	SGPT_ALT	0
1	16	22FEB2007:00:00:00	SGPT_ALT	0
1	24	31OCT2007:00:00:00	SGPT_ALT	0
1	36	29OCT2008:00:00:00	SGPT_ALT	0
1	48	30SEP2009:00:00:00	SGPT_ALT	0
1	60	13OCT2010:00:00:00	SGPT_ALT	0
1	72	12OCT2011:00:00:00	SGPT_ALT	0
1	84	17OCT2012:00:00:00	SGPT_ALT	1
1	96	28OCT2013:00:00:00	SGPT_ALT	0
2	1	21NOV2005:00:00:00	SGPT_ALT	0
2	4	20FEB2006:00:00:00	SGPT_ALT	0
2	8	10JUL2006:00:00:00	SGPT_ALT	0
2	12	23OCT2006:00:00:00	SGPT_ALT	0
2	16	26FEB2007:00:00:00	SGPT_ALT	0
2	24	22OCT2007:00:00:00	SGPT_ALT	0
2	36	21OCT2008:00:00:00	SGPT_ALT	0
2	48	08OCT2009:00:00:00	SGPT_ALT	1
2	60	13OCT2010:00:00:00	SGPT_ALT	0
2	72	13OCT2011:00:00:00	SGPT_ALT	1
2	84	15OCT2012:00:00:00	SGPT_ALT	1
2	96	22OCT2013:00:00:00	SGPT_ALT	1
3	0	04NOV2005:00:00:00	SGPT_ALT	1
3	1	06DEC2005:00:00:00	SGPT_ALT	1
4	1	08DEC2005:00:00:00	SGPT_ALT	0
4	4	07MAR2006:00:00:00	SGPT_ALT	0
4	8	22JUN2006:00:00:00	SGPT_ALT	0
4	12	17OCT2006:00:00:00	SGPT_ALT	0
5	1	07DEC2005:00:00:00	SGPT_ALT	0
5	4	06MAR2006:00:00:00	SGPT_ALT	1
5	8	10JUL2006:00:00:00	SGPT_ALT	1
5	12	10NOV2006:00:00:00	SGPT_ALT	1
5	16	08MAR2007:00:00:00	SGPT_ALT	0
5	20	27JUN2007:00:00:00	SGPT_ALT	0
5	24	26OCT2007:00:00:00	SGPT_ALT	0
5	36	15OCT2008:00:00:00	SGPT_ALT	0
5	48	16OCT2009:00:00:00	SGPT_ALT	0
5	60	02NOV2010:00:00:00	SGPT_ALT	0
5	60	01NOV2011:00:00:00	SGPT_ALT	1

OUTPUT NEEDED

SUBJNO	VISMON	SAMPDT	TESTCODE	ALTX3ULN	FLAG
1	1	30NOV2005:00:00:00	SGPT_ALT	0
1	4	01MAR2006:00:00:00	SGPT_ALT	0
1	8	28JUN2006:00:00:00	SGPT_ALT	0
1	12	25OCT2006:00:00:00	SGPT_ALT	0
1	16	22FEB2007:00:00:00	SGPT_ALT	0
1	24	31OCT2007:00:00:00	SGPT_ALT	0
1	36	29OCT2008:00:00:00	SGPT_ALT	0
1	48	30SEP2009:00:00:00	SGPT_ALT	0
1	60	13OCT2010:00:00:00	SGPT_ALT	0
1	72	12OCT2011:00:00:00	SGPT_ALT	0
1	84	17OCT2012:00:00:00	SGPT_ALT	1
1	96	28OCT2013:00:00:00	SGPT_ALT	0
2	1	21NOV2005:00:00:00	SGPT_ALT	0
2	4	20FEB2006:00:00:00	SGPT_ALT	0
2	8	10JUL2006:00:00:00	SGPT_ALT	0
2	12	23OCT2006:00:00:00	SGPT_ALT	0
2	16	26FEB2007:00:00:00	SGPT_ALT	0
2	24	22OCT2007:00:00:00	SGPT_ALT	0
2	36	21OCT2008:00:00:00	SGPT_ALT	0
2	48	08OCT2009:00:00:00	SGPT_ALT	1
2	60	13OCT2010:00:00:00	SGPT_ALT	0
2	72	13OCT2011:00:00:00	SGPT_ALT	1	Y
2	84	15OCT2012:00:00:00	SGPT_ALT	1
2	96	22OCT2013:00:00:00	SGPT_ALT	1
3	0	04NOV2005:00:00:00	SGPT_ALT	1	Y
3	1	06DEC2005:00:00:00	SGPT_ALT	1
4	1	08DEC2005:00:00:00	SGPT_ALT	0
4	4	07MAR2006:00:00:00	SGPT_ALT	0
4	8	22JUN2006:00:00:00	SGPT_ALT	0
4	12	17OCT2006:00:00:00	SGPT_ALT	0
5	1	07DEC2005:00:00:00	SGPT_ALT	0
5	4	06MAR2006:00:00:00	SGPT_ALT	1	Y
5	8	10JUL2006:00:00:00	SGPT_ALT	1
5	12	10NOV2006:00:00:00	SGPT_ALT	1
5	16	08MAR2007:00:00:00	SGPT_ALT	0
5	20	27JUN2007:00:00:00	SGPT_ALT	0
5	24	26OCT2007:00:00:00	SGPT_ALT	0
5	36	15OCT2008:00:00:00	SGPT_ALT	0
5	48	16OCT2009:00:00:00	SGPT_ALT	0
5	60	02NOV2010:00:00:00	SGPT_ALT	0
5	60	01NOV2011:00:00:00	SGPT_ALT	1

[Linlin]

example:

data have;

input id vismon alt;

cards;

1 1 0

1 4 0

2 60 0

2 72 1

2 84 1

2 96 1

;

data want;

set have;

length new $1;

by id alt;

if alt=1 and first.alt and not last.alt then new='Y';

proc print;run;

[Almighty · Accepted solution]

I found the way to do that and below code is working:  Gurus can help us improving the code inorder to increase the efficiency.

Thanks

data want  ;

  set have ;

  by subjno  altx3uln notsorted;

  if altx3uln=1 and (first.altx3uln=0 or last.altx3uln=0) then Consecutive=1;

  else Consecutive=0;

run;

proc sort data=have nodupkey; by subjno  descending ALTx3ULN descending Consecutive; run;

proc sort data=have nodupkey; by subjno; run;