Two consecutive visit records, Flag first record

Almighty — Tue, 20 May 2014 17:23:34 GMT

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

Re: Two consecutive visit records, Flag first record

Linlin — Tue, 20 May 2014 18:16:38 GMT

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;

Re: Two consecutive visit records, Flag first record

Almighty — Tue, 20 May 2014 20:55:51 GMT

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;

topic Two consecutive visit records, Flag first record in SAS Procedures

Two consecutive visit records, Flag first record

Re: Two consecutive visit records, Flag first record

Re: Two consecutive visit records, Flag first record