Help using Base SAS procedures

sas query

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

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: 7,487

Re: sas query

Posted in reply to rakeshvvv

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;

View solution in original post


All Replies
PROC Star
Posts: 7,487

Re: sas query

Posted in reply to rakeshvvv

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

Frequent Contributor
Posts: 145

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: 7,487

Re: sas query

Posted in reply to rakeshvvv

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: 145

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: 7,487

Re: sas query

Posted in reply to rakeshvvv

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: 145

Re: sas query

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

Super Contributor
Posts: 308

Re: sas query

Posted in reply to rakeshvvv

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 373 views
  • 6 likes
  • 3 in conversation