Help using Base SAS procedures

query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

query

Hi,

I have a dataset with variables 4 variables id, trt, dosmod, date with ID as key variable.

I have to write query in such a way that,

For every id, when dosmod=’ INTERRUPTION OR DELAY”. The next record for that id must be less then 21 days less than date of ’ INTERRUPTION OR DELAY. If is more then 21 days,we should output those discrepancies.

We have to output the rerecords highlighted in red.

100010001

XXXXXXXX

DRUG WITHDRAWN

11/18/2014

100010001

XXXXXXXX

DRUG WITHDRAWN

11/18/2014

100010002

XXXXXXXX

REDUCTION

10/13/2014

100010002

XXXXXXXX

INTERRUPTION OR DELAY

10/20/2014

100010002

XXXXXXXX

DRUG WITHDRAWN

10/21/2014

100010002

XXXXXXXX

DRUG WITHDRAWN

10/21/2014

100010002

XXXXXXXX

DRUG WITHDRAWN

10/21/2014

100020002

XXXXXXXX

INTERRUPTION OR DELAY

2/11/2015

100030002

XXXXXXXX

INTERRUPTION OR DELAY

10/15/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

10/24/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

11/5/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

11/26/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

11/26/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

11/26/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

12/3/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

12/24/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

12/24/2014

100030002

XXXXXXXX

INTERRUPTION OR DELAY

12/31/2014

140010002

XXXXXXXX

REDUCTION

9/4/2014

140010002

XXXXXXXX

INTERRUPTION OR DELAY

9/11/2014

140010002

XXXXXXXX

REDUCTION

10/19/2014

140020001

XXXXXXXX

INTERRUPTION OR DELAY

11/24/2014

140020001

XXXXXXXX

INTERRUPTION OR DELAY

11/24/2014

140020001

XXXXXXXX

REDUCTION

12/24/2014


Accepted Solutions
Solution
‎02-26-2015 04:30 PM
Contributor
Posts: 52

Re: query

Posted in reply to rakeshvvv

A solution.

first, simulate a dataset.

second, run a solution.

/************************/

/***simulated dataset ***/

/************************/

data have(keep=id dosmod zDate);
  length id 8. dosmod $25.;
  format zDate date9.;
  do id = 100 to 105;
     zDate = 20000;
     do j = 1 to 20;
        zDate+ int(60*ranuni(3));
        k=int(3*ranuni(3));
             if k = 0 then dosmod = 'INTERRUPTION OR DELAY';
        else if k = 1 then dosmod = 'REDUCTION';
        else if k = 2 then dosmod = 'DRUG WITHDRAWN';
        output;
     end;
  end;
run;

/**********************/

/*** run a solution ***/

/**********************/
proc sort data=have; by id descending zDate; run;

data want;
  set have;
  by id;
  length dosmod_0 $25.;
  format zDate_0 date9.;
  retain iter dosmod_0 zDate_0;

  if first.id then do; iter=0; zDate_0=.; dosmod_0=''; end;
  iter+1;
  diff_dy = (zDate_0-zDate);
  if (iter>1) and
     (dosmod_0='REDUCTION') and
     (dosmod='INTERRUPTION OR DELAY') and
  (diff_dy >=21) then output;
  zDate_0=zDate; dosmod_0=dosmod;
run;

View solution in original post


All Replies
Solution
‎02-26-2015 04:30 PM
Contributor
Posts: 52

Re: query

Posted in reply to rakeshvvv

A solution.

first, simulate a dataset.

second, run a solution.

/************************/

/***simulated dataset ***/

/************************/

data have(keep=id dosmod zDate);
  length id 8. dosmod $25.;
  format zDate date9.;
  do id = 100 to 105;
     zDate = 20000;
     do j = 1 to 20;
        zDate+ int(60*ranuni(3));
        k=int(3*ranuni(3));
             if k = 0 then dosmod = 'INTERRUPTION OR DELAY';
        else if k = 1 then dosmod = 'REDUCTION';
        else if k = 2 then dosmod = 'DRUG WITHDRAWN';
        output;
     end;
  end;
run;

/**********************/

/*** run a solution ***/

/**********************/
proc sort data=have; by id descending zDate; run;

data want;
  set have;
  by id;
  length dosmod_0 $25.;
  format zDate_0 date9.;
  retain iter dosmod_0 zDate_0;

  if first.id then do; iter=0; zDate_0=.; dosmod_0=''; end;
  iter+1;
  diff_dy = (zDate_0-zDate);
  if (iter>1) and
     (dosmod_0='REDUCTION') and
     (dosmod='INTERRUPTION OR DELAY') and
  (diff_dy >=21) then output;
  zDate_0=zDate; dosmod_0=dosmod;
run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 181 views
  • 1 like
  • 2 in conversation