Help using Base SAS procedures

Two consecutive visit records, Flag first record

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Two consecutive visit records, Flag first record

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

SUBJNOVISMONSAMPDTTESTCODEALTX3ULN
1130NOV2005:00:00:00SGPT_ALT0
1401MAR2006:00:00:00SGPT_ALT0
1828JUN2006:00:00:00SGPT_ALT0
11225OCT2006:00:00:00SGPT_ALT0
11622FEB2007:00:00:00SGPT_ALT0
12431OCT2007:00:00:00SGPT_ALT0
13629OCT2008:00:00:00SGPT_ALT0
14830SEP2009:00:00:00SGPT_ALT0
16013OCT2010:00:00:00SGPT_ALT0
17212OCT2011:00:00:00SGPT_ALT0
18417OCT2012:00:00:00SGPT_ALT1
19628OCT2013:00:00:00SGPT_ALT0
2121NOV2005:00:00:00SGPT_ALT0
2420FEB2006:00:00:00SGPT_ALT0
2810JUL2006:00:00:00SGPT_ALT0
21223OCT2006:00:00:00SGPT_ALT0
21626FEB2007:00:00:00SGPT_ALT0
22422OCT2007:00:00:00SGPT_ALT0
23621OCT2008:00:00:00SGPT_ALT0
24808OCT2009:00:00:00SGPT_ALT1
26013OCT2010:00:00:00SGPT_ALT0
27213OCT2011:00:00:00SGPT_ALT1
28415OCT2012:00:00:00SGPT_ALT1
29622OCT2013:00:00:00SGPT_ALT1
3004NOV2005:00:00:00SGPT_ALT1
3106DEC2005:00:00:00SGPT_ALT1
4108DEC2005:00:00:00SGPT_ALT0
4407MAR2006:00:00:00SGPT_ALT0
4822JUN2006:00:00:00SGPT_ALT0
41217OCT2006:00:00:00SGPT_ALT0
5107DEC2005:00:00:00SGPT_ALT0
5406MAR2006:00:00:00SGPT_ALT1
5810JUL2006:00:00:00SGPT_ALT1
51210NOV2006:00:00:00SGPT_ALT1
51608MAR2007:00:00:00SGPT_ALT0
52027JUN2007:00:00:00SGPT_ALT0
52426OCT2007:00:00:00SGPT_ALT0
53615OCT2008:00:00:00SGPT_ALT0
54816OCT2009:00:00:00SGPT_ALT0
56002NOV2010:00:00:00SGPT_ALT0
56001NOV2011:00:00:00SGPT_ALT1

OUTPUT NEEDED

SUBJNOVISMONSAMPDTTESTCODEALTX3ULNFLAG
1130NOV2005:00:00:00SGPT_ALT0
1401MAR2006:00:00:00SGPT_ALT0
1828JUN2006:00:00:00SGPT_ALT0
11225OCT2006:00:00:00SGPT_ALT0
11622FEB2007:00:00:00SGPT_ALT0
12431OCT2007:00:00:00SGPT_ALT0
13629OCT2008:00:00:00SGPT_ALT0
14830SEP2009:00:00:00SGPT_ALT0
16013OCT2010:00:00:00SGPT_ALT0
17212OCT2011:00:00:00SGPT_ALT0
18417OCT2012:00:00:00SGPT_ALT1
19628OCT2013:00:00:00SGPT_ALT0
2121NOV2005:00:00:00SGPT_ALT0
2420FEB2006:00:00:00SGPT_ALT0
2810JUL2006:00:00:00SGPT_ALT0
21223OCT2006:00:00:00SGPT_ALT0
21626FEB2007:00:00:00SGPT_ALT0
22422OCT2007:00:00:00SGPT_ALT0
23621OCT2008:00:00:00SGPT_ALT0
24808OCT2009:00:00:00SGPT_ALT1
26013OCT2010:00:00:00SGPT_ALT0
27213OCT2011:00:00:00SGPT_ALT1Y
28415OCT2012:00:00:00SGPT_ALT1
29622OCT2013:00:00:00SGPT_ALT1
3004NOV2005:00:00:00SGPT_ALT1Y
3106DEC2005:00:00:00SGPT_ALT1
4108DEC2005:00:00:00SGPT_ALT0
4407MAR2006:00:00:00SGPT_ALT0
4822JUN2006:00:00:00SGPT_ALT0
41217OCT2006:00:00:00SGPT_ALT0
5107DEC2005:00:00:00SGPT_ALT0
5406MAR2006:00:00:00SGPT_ALT1Y
5810JUL2006:00:00:00SGPT_ALT1
51210NOV2006:00:00:00SGPT_ALT1
51608MAR2007:00:00:00SGPT_ALT0
52027JUN2007:00:00:00SGPT_ALT0
52426OCT2007:00:00:00SGPT_ALT0
53615OCT2008:00:00:00SGPT_ALT0
54816OCT2009:00:00:00SGPT_ALT0
56002NOV2010:00:00:00SGPT_ALT0
56001NOV2011:00:00:00SGPT_ALT1

Accepted Solutions
Solution
‎05-20-2014 04:55 PM
Contributor
Posts: 32

Re: Two consecutive visit records, Flag first record

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;

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Two consecutive visit records, Flag first record

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;

Solution
‎05-20-2014 04:55 PM
Contributor
Posts: 32

Re: Two consecutive visit records, Flag first record

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 405 views
  • 1 like
  • 2 in conversation