topic Re: sas query in SAS Procedures

sas query

rakeshvvv — Wed, 04 Mar 2015 20:38:29 GMT

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

Re: sas query

art297 — Wed, 04 Mar 2015 20:54:15 GMT

Have you tried to solve your problem? If so, post the code that you tried.

Re: sas query

rakeshvvv — Thu, 05 Mar 2015 14:38:26 GMT

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;

Re: sas query

art297 — Thu, 05 Mar 2015 15:15:54 GMT

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;

Re: sas query

rakeshvvv — Thu, 05 Mar 2015 15:36:22 GMT

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

Re: sas query

art297 — Thu, 05 Mar 2015 15:45:04 GMT

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;

Re: sas query

rakeshvvv — Thu, 05 Mar 2015 15:50:57 GMT

Thanks....Perfect for me.....

Re: sas query

Loko — Thu, 05 Mar 2015 15:59:52 GMT

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;