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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: