BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sam369
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

9 REPLIES 9
ballardw
Super User

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?

sam369
Obsidian | Level 7

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

ChrisNZ
Tourmaline | Level 20

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
Opal | Level 21

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
Super User

(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
Tourmaline | Level 20

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

Kurt_Bremser
Super User

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
Obsidian | Level 7

Thank you All

Sam

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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