Help using Base SAS procedures

check dates

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

check dates

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   v120jun2012
100  xxx   v227aug2012
100  xxx   v315sep2012
100  xxx   v420aug2012  1
100  yyy   v120jun2012

100  yyy   v2   

100  yyy   v315sep2012
100  yyy   v415sep2012
100  zzz   v120jun2012
100  zzz   v227aug2012
100  zzz   v310mar2012   1
100  zzz   v315sep2012
100  aaa   v120jun2012
100  aaa   v227aug2011   1
100  aaa   v315sep2012
100  aaa   v425dec2012
100  bbb   v120jun2013    1
100  bbb   v227aug2012   1
100  bbb   v315sep2012  

1

Thanks

Sam


Accepted Solutions
Solution
‎08-18-2015 05:14 AM
Super User
Posts: 7,762

Re: check dates

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 11,343

Re: check dates

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   v120jun2013   1

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

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

Regular Contributor
Posts: 168

Re: check dates

Hi Ballardw,

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

sub  grp   vis   dt               flg

100  bbb   v120jun2013  

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

PROC Star
Posts: 1,759

Re: check dates

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;

Super User
Posts: 5,498

Re: check dates

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.

Super User
Posts: 7,762

Re: check dates

(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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 1,759

Re: check dates

Posted in reply to KurtBremser

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

Solution
‎08-18-2015 05:14 AM
Super User
Posts: 7,762

Re: check dates

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 168

Re: check dates

Posted in reply to KurtBremser

Thank you All

Sam

Super User
Super User
Posts: 7,942

Re: check dates

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 565 views
  • 6 likes
  • 6 in conversation