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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: