query

rakeshvvv — Thu, 26 Feb 2015 19:40:01 GMT

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

Re: query

billfish — Thu, 26 Feb 2015 21:30:43 GMT

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;

topic query in SAS Procedures

query

Re: query