check dates

[sam369]

Hi All,

i need to check the date with previous visit in the group. i have visits up to 50, and different from each group. below is how data look like

Have:

data have;

input sub grp vis dt;

cards;

100  xxx   v1    20jun2012

100  xxx   v2    27aug2012

100  xxx   v3    15sep2012

100  xxx   v4    20aug2012

100  yyy   v1    20jun2012

100  yyy   v2   

100  yyy   v3    15sep2012

100  yyy   v4    15sep2012

100  zzz   v1    20jun2012

100  zzz   v2    27aug2012

100  zzz   v3    10mar2012

100  zzz   v3    15sep2012

100  aaa   v1    20jun2012

100  aaa   v2    27aug2011

100  aaa   v3    15sep2012

100  aaa   v4    25dec2012

100  bbb   v1    20jun2013

100  bbb   v2    27aug2012

100  bbb   v3    15sep2012

want:

isub grp vis dt

flg;

100  xxx   v1	20jun2012
100  xxx   v2	27aug2012
100  xxx   v3	15sep2012
100  xxx   v4	20aug2012	1
100  yyy   v1	20jun2012

100  yyy   v2   

100  yyy   v3	15sep2012
100  yyy   v4	15sep2012
100  zzz   v1	20jun2012
100  zzz   v2	27aug2012
100  zzz   v3	10mar2012	1
100  zzz   v3	15sep2012
100  aaa   v1	20jun2012
100  aaa   v2	27aug2011	1
100  aaa   v3	15sep2012
100  aaa   v4	25dec2012
100  bbb   v1	20jun2013	1
100  bbb   v2	27aug2012	1
100  bbb   v3	15sep2012	1

Thanks

Sam

[ballardw]

What is the rule for flagging? It looks like for some that the flag is when the date is earlier than the previous but you have

100  bbb   v1

20jun2013

1

which is the first visit, so does not match that rule.

Also, are your dates currently SAS date values or character?

[sam369]

Hi Ballardw,

Dates are in character format. yes you are right, the subject doesn't have flg

sub  grp   vis   dt               flg

100  bbb   v1

20jun2013

The rule for the flg is, to check the dates with previous dates in that grp. if the visit date is after the previous visit date i need to flg them .

Thanks

Sam

[ChrisNZ]

Use a read-ahead technique. For eg

data WANT;

  merge HAVE

        HAVE(firstobs=2

             keep    =SUB GRP DT

             rename  =(SUB=NEXT_SUB GRP=NEXT_GRP DT=NEXT_DT)) ;

  if SUB=NEXT_SUB & GRP=NEXT_GRP & DT>NEXT_DT then FLG=1;

  drop NEXT:;

run;

[Astounding]

You'll have to verify that your solution works properly when a date is missing.  That being said, here's an approach:

data want;

  set have;

  by sub vis;

  if first.sub then prior_dt = input(dt, date9.);

  else if dt > ' ' then do;

     if input(dt, date9.) < prior_dt then flag=1;

     else prior_dt = input(dt, date9.);

  end;

  retain prior_dt;

run;

It's untested, but you can give it a try.

Good luck.

[Kurt_Bremser]

(convert dt to a SAS date variable before processing)

data want (drop=olddate);

set have;

by grp notsorted;

retain olddate;

if first.grp then olddate = .;

if olddate > dt then flg = 1;

if dt ne . then olddate = dt;

run;

[ChrisNZ]

The flag must be set on the preceding record in the example given.

[Kurt_Bremser · Accepted solution]

As I see it, the flag needs to be set on the succeeding record with the earlier date.

While testing, I found I needed to do a slight correction, so now the code is:

data want (drop=olddate);

set have;

by grp notsorted;

retain olddate;

if first.grp then olddate = .;

if olddate > dt and dt ne . then flg = 1;

if dt ne . then olddate = dt;

run;

[sam369]

Thank you All

Sam

[RW9]

Hi,

I would also have date as numeric, and visit as numeric - have a look at CDISC standards, Visit and Visitnumber.  If you have them in the correct format then sort by date and if visitnum ne lag(visitnum)+1 then error.