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

I have dataset with 4 variables

Id, trt, dosmod, days…..I want to query in such a way that for a particular id and trt(group by)….when dosmod=’ INTERRUPTION OR DELAY’ then the next immediate record for that id and trt should be within 7 days of dosmod=’ INTERRUPTION OR DELAY’ or next immediate record should have dosmod=’ DRUG WITHDRAWN’….I SHOULD OUTPUT THOSE HIGHLIGHTED IN YELLOW


100030002

xxxxxxxx

NONE

1

100030002

xxxxxxxx

2

100030002

xxxxxxxx

NONE

8

100030002

xxxxxxxx

NONE

15

100030002

xxxxxxxx

INTERRUPTION OR DELAY

24

100030002

xxxxxxxx

NONE

31

100030002

xxxxxxxx

INTERRUPTION OR DELAY

33

100030002

xxxxxxxx

NONE

38

100030002

xxxxxxxx

INTERRUPTION OR DELAY

45

100030002

xxxxxxxx

NONE

52

100030002

xxxxxxxx

NONE

59

100030002

xxxxxxxx

INTERRUPTION OR DELAY

66

100030002

xxxxxxxx

INTERRUPTION OR DELAY

73

100030002

xxxxxxxx

NONE

80

100030002

xxxxxxxx

NONE

87

100030002

xxxxxxxx

NONE

94

100030002

xxxxxxxx

INTERRUPTION OR DELAY

101

100030002

xxxxxxxx

NONE

107

100030002

xxxxxxxx

NONE

113

100030002

yyyyyyyyy

NONE

1

100030002

yyyyyyyyy

NONE

24

100030002

yyyyyyyyy

NONE

45

100030002

yyyyyyyyy

INTERRUPTION OR DELAY

66

100030002

yyyyyyyyy

DRUG WITHDRAWN

73

100030002

yyyyyyyyy

INTERRUPTION OR DELAY

94

100030002

yyyyyyyyy

NONE

99

100030002

yyyyyyyyy

NONE

121

100030002

zzzzzzzz

NONE

1

100030002

zzzzzzzz

NONE

2

100030002

zzzzzzzz

NONE

3

100030002

zzzzzzzz

NONE

24

100030002

zzzzzzzz

NONE

25

100030002

zzzzzzzz

NONE

26

100030002

zzzzzzzz

NONE

45

100030002

zzzzzzzz

NONE

46

100030002

zzzzzzzz

NONE

47

100030002

zzzzzzzz

INTERRUPTION OR DELAY

66

100030002

zzzzzzzz

DRUG WITHDRAWN

73

100030002

zzzzzzzz

NONE

74

100030002

zzzzzzzz

NONE

75

100030002

zzzzzzzz

INTERRUPTION OR DELAY

94

100030002

zzzzzzzz

NONE

99

100030002

zzzzzzzz

NONE

100

100030002

zzzzzzzz

NONE

101

100030002

zzzzzzzz

NONE

121

100030002

zzzzzzzz

NONE

122

100030002

zzzzzzzz

NONE

123

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

proc sort data=have;

by id trt days;

run;

data want (keep=id trt idosmod iday rename=(idosmod=dosmod iday=days));

set have;

by id trt;

idosmod = lag(dosmod);

iday = lag(days);

if not first.trt

then do;

  if

    idosmod = 'INTERRUPTION OR DELAY' and

    dosmod ne 'DRUG WITHDRAWN' and

    dosmod ne 'INTERRUPTION OR DELAY' and

    days - iday >= 6

  then do;

    output;

    idosmod = dosmod;

    iday = days;

    output;

  end;

end;

run;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

proc sort data=have;

by id trt days;

run;

data want (keep=id trt idosmod iday rename=(idosmod=dosmod iday=days));

set have;

by id trt;

idosmod = lag(dosmod);

iday = lag(days);

if not first.trt

then do;

  if

    idosmod = 'INTERRUPTION OR DELAY' and

    dosmod ne 'DRUG WITHDRAWN' and

    dosmod ne 'INTERRUPTION OR DELAY' and

    days - iday >= 6

  then do;

    output;

    idosmod = dosmod;

    iday = days;

    output;

  end;

end;

run;

rakeshvvv
Quartz | Level 8

Hi, Thanks for the reply......the solution works near perfect for me....only additional thing which i would require for representation purpose is that...... I am adding STDTC(date variable) to the dataset...when I included that variable results are coming as expected except for the dates..the days difference should reflect in the dates part(STDTC). can you help me on this .....i was using below code...

proc sort data=have;

by id trt stdtc days;

run;

data want (keep=id trt idosmod stdtc iday rename=(idosmod=dosmod iday=days));

set have;

by id trt;

idosmod = lag(dosmod);

iday = lag(days);

if not first.trt

then do;

  if

    idosmod = 'INTERRUPTION OR DELAY' and

    dosmod ne 'DRUG WITHDRAWN' and

    dosmod ne 'INTERRUPTION OR DELAY' and

    days - iday >= 6

  then do;

    output;

    idosmod = dosmod;

    iday = days;

    output;

  end;

end;

run;

Kurt_Bremser
Super User

You need to do the same with stdtc that I did with dosmod and days. Create a temporary variable that holds the value from the lag() function and rename it in the output dataset.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 704 views
  • 0 likes
  • 2 in conversation