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)….
If more than two consecutive dose reductions(DOSMOD=’ REDUCTION’) occurs for id, trt then it is discrepancy. We should output these discrepancies where DOSMOD=’ REDUCTION’ occurs consecutively more then twice for id and trt.
Thanks
Here is one way:
data have;
informat dosmod $9.;
input Id trt dosmod days;
cards;
1 1 50ml 1
1 1 REDUCTION 2
1 1 50ml 3
1 1 REDUCTION 4
1 1 REDUCTION 5
1 1 REDUCTION 6
1 1 50ml 7
2 1 50ml 1
2 1 REDUCTION 2
2 1 50ml 3
2 1 REDUCTION 4
2 1 REDUCTION 5
2 1 50ml 6
3 1 REDUCTION 1
3 1 REDUCTION 2
3 1 50ml 3
3 1 REDUCTION 4
3 1 REDUCTION 5
3 1 REDUCTION 6
3 1 50ml 7
;
proc sort data=have;
by id trt days;
run;
data want;
do until (last.trt);
set have;
by id trt;
if first.trt then counter=0;
if dosmod eq 'REDUCTION' then counter+1;
else if counter lt 3 then counter=0;
end;
do until (last.trt);
set have;
by id trt;
if counter ge 3 then output;
end;
run;
Have you tried to solve your problem? If so, post the code that you tried.
I have tried tried the following way.....
Proc sql;
create table want as
select id, trt, dosmod,days
from x
group by id, trt
having DOSMOD=’ REDUCTION and count(DOSMOD) >= 3; /* I would need 3 consecutive occurences where as i was able to do it for 3occurances overall*/
QUIT;
Here is one way:
data have;
informat dosmod $9.;
input Id trt dosmod days;
cards;
1 1 50ml 1
1 1 REDUCTION 2
1 1 50ml 3
1 1 REDUCTION 4
1 1 REDUCTION 5
1 1 REDUCTION 6
1 1 50ml 7
2 1 50ml 1
2 1 REDUCTION 2
2 1 50ml 3
2 1 REDUCTION 4
2 1 REDUCTION 5
2 1 50ml 6
3 1 REDUCTION 1
3 1 REDUCTION 2
3 1 50ml 3
3 1 REDUCTION 4
3 1 REDUCTION 5
3 1 REDUCTION 6
3 1 50ml 7
;
proc sort data=have;
by id trt days;
run;
data want;
do until (last.trt);
set have;
by id trt;
if first.trt then counter=0;
if dosmod eq 'REDUCTION' then counter+1;
else if counter lt 3 then counter=0;
end;
do until (last.trt);
set have;
by id trt;
if counter ge 3 then output;
end;
run;
Thanks for the reply...can we make small enhancement to have only those recoreds which have a discrepancy.......for previous example...ideally i would prefer the following output.....
REDUCTION 1 1 4 3
REDUCTION 1 1 5 3
REDUCTION 1 1 6 3
REDUCTION 3 1 4 3
REDUCTION 3 1 5 3
REDUCTION 3 1 6 3
Again, here is one way:
data have;
informat dosmod $9.;
input Id trt dosmod days;
cards;
1 1 50ml 1
1 1 REDUCTION 2
1 1 50ml 3
1 1 REDUCTION 4
1 1 REDUCTION 5
1 1 REDUCTION 6
1 1 50ml 7
2 1 50ml 1
2 1 REDUCTION 2
2 1 50ml 3
2 1 REDUCTION 4
2 1 REDUCTION 5
2 1 50ml 6
3 1 REDUCTION 1
3 1 REDUCTION 2
3 1 50ml 3
3 1 REDUCTION 4
3 1 REDUCTION 5
3 1 REDUCTION 6
3 1 50ml 7
;
proc sort data=have;
by id trt days;
run;
data want;
set have;
by id trt;
if first.trt then counter=0;
if dosmod eq 'REDUCTION' then counter+1;
else counter=0;
run;
proc sort data=want;
by id trt descending days;
run;
data want (drop=counter wantit);
set want;
by id trt;
retain wantit;
if first.trt then wantit=0;
if wantit eq 0 and counter ge 3 then do;
wantit=1;
output;
end;
else if wantit and counter gt 0 then do;
output;
if counter eq 1 then wantit=0;
end;
run;
Thanks....Perfect for me.....
Hello,
Using Arthur's data example and a hash solution:
proc sort data=have;
by id trt days;
run;
data want;
if 0 then set have;
if _n_=1 then
do;
declare hash a(multidata: 'Y');
a.definekey('id', 'trt');
a.definedone();
end;
set have end=last;
by id trt;
if dosmod ne 'REDUCTION' then count=0;
else if count<3
then count+1;
if count >= 3 then a.add();
if last then do until(new_last);
set have end=new_last;
if a.find()=0 then output;
end;
drop count;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.