BookmarkSubscribeRSS Feed
karns
Calcite | Level 5

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!

7 REPLIES 7
ballardw
Super User

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Super User

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

Ksharp
Super User

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
Calcite | Level 5

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
Super User

"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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1690 views
  • 3 likes
  • 5 in conversation