Programming the statistical procedures from SAS

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

Reply
Occasional Contributor
Posts: 5

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

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!

Super User
Posts: 10,843

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

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.

Occasional Contributor
Posts: 5

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

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!

Super Contributor
Posts: 275

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

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;

Super User
Posts: 18,528

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

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

Super User
Posts: 9,766

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

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

Occasional Contributor
Posts: 5

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

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!

Super User
Posts: 9,766

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

"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

Ask a Question
Discussion stats
  • 7 replies
  • 346 views
  • 3 likes
  • 5 in conversation