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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.