sas query

[rakeshvvv]

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

[art297]

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

[rakeshvvv]

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;

[art297 · Accepted solution]

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;

[rakeshvvv]

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

[art297]

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;

[rakeshvvv]

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

[Loko]

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;