topic Re: How to select Max value and max value -1 and +1 in Statistical Procedures

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

karns — Thu, 05 Jun 2014 21:42:54 GMT

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

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!

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

ballardw — Thu, 05 Jun 2014 22:36:49 GMT

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.

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

karns — Thu, 05 Jun 2014 22:45:25 GMT

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!

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

slchen — Thu, 05 Jun 2014 23:48:42 GMT

data have;

input PID Value;

datalines;

123 0

123 0.01

123 0.02

123 0.01

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;

keep pid value;

run;

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

Reeza — Thu, 05 Jun 2014 23:58:25 GMT

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

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

Ksharp — Fri, 06 Jun 2014 12:47:47 GMT

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

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

karns — Fri, 06 Jun 2014 13:46:37 GMT

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!

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

Ksharp — Sat, 07 Jun 2014 06:51:28 GMT

"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