How to select Max value and max value -1 and +1

[karns]

Hello,

I'm wondering if someone might be able to help me. I'd like to select the max value of a particular variable. In addition, I want to select the two values on either side of the max value (=/- max value).

PID       Value

123      0

123      0.01

123      0.02

123      0.01

123      0

123      0

This means I want the final data set to look like:

PID      Value

123      0.01

123      0.02

123      0.01

Any help would be much appreciated!

[ballardw]

One way:

data have;
input PID Value;
datalines;
123      0
123      0.01
123      0.02
123      0.01
123      0
123      0
;
run;
/*add a line identifier as SQl doesn't really understand order*/
Data temp;
     set have;
     LineID= _n_;
run;

proc sql;
     create table want as
     select c.PID, c.Value
     from temp as c , (select a.lineid
    from temp as a inner join (select max(value) as value from temp) as b
    on a.value=b.value) as d
     where c.lineid between (d.LineID -1) and (d.LineID + 1);
quit;

but if you have multiple PID then there are several changes to be made.

[karns]

Thank you very much for your reply! I was in the process of writing similar programming. I do have multiple PIDs. Would you be able to explain the changes that would need to be made?

Thanks again!

[slchen]

data have;

input PID Value;

datalines;

123      0

123      0.01

123      0.02

123      0.01

123      0

123      0

234      0.12

234      0.04

234      0.6

234      0.3

;

run;

data want;

do i=1 by 1 until(last.pid);

set have;

by pid;

array val [100] _temporary_;

val(i)=value;

if last.pid then do;

     max_val=largest(1,of val(*));

    point=whichn(max_val,of val(*));

  do j=point-1,point,point+1;

    value=val(j);

    output;

  end;

  end;

end;

keep pid value;

run;

[Reeza]

Can you have ties for the maximum? If so, what would you do in that scenario?

[Ksharp]

Include Ties of maximum value.

data have;
input PID Value;
datalines;
123      0
123      0.01
123      0.02
123      0.02
123      0.01
123      0
123      0
;
run;
data have;
 set have;
 n+1;
run;
proc sql;
create table n as
 select n from have group by pid having value=max(value);
quit;
data n;
 set n;
 output;
 n=n-1;output;
 n=n+2;output;
run;
data want;
 if _n_ eq 1 then do;
  declare hash h(dataset:'n');
   h.definekey('n');
   h.definedone();
 end;
  set have;
if h.check()=0;
run;

Xia Keshan

[karns]

Hi Ksharp,

Thanks for your reply. Would you mind answering a few questions?

I'm have an issue with this line: h.definedone();

Is there something in particular that is supposed to go into the parentheses? Same thing with this line: if h.check()=0;

Does this code select the max value and the value on either side of the max value? Also, if I wanted to also select the minimum value, would I just added value=min(value);?

Thank you!

[Ksharp]

"Is there something in particular that is supposed to go into the parentheses? Same thing with this line: if h.check()=0;"

Yes. there are some parameters in it. You need to check it in sas documentation( dictionary ).

"Does this code select the max value and the value on either side of the max value? "

Yes. include if there were multiple maximum value .

' if I wanted to also select the minimum value, would I just added value=min(value);?"

Yes.If you also want to do it like maximum did .

Xia Keshan