## sas query

Solved
Frequent Contributor
Posts: 147

# sas query

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

Accepted Solutions
Solution
‎03-05-2015 10:15 AM
PROC Star
Posts: 8,164

## Re: sas query

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;

All Replies
PROC Star
Posts: 8,164

## Re: sas query

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

Frequent Contributor
Posts: 147

## Re: sas query

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;

Solution
‎03-05-2015 10:15 AM
PROC Star
Posts: 8,164

## Re: sas query

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;

Frequent Contributor
Posts: 147

## Re: sas query

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

PROC Star
Posts: 8,164

## Re: sas query

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;

Frequent Contributor
Posts: 147

## Re: sas query

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

Super Contributor
Posts: 319

## Re: sas query

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;

🔒 This topic is solved and locked.