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