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 |
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.