CODEWITHSUNDEEP
sasdatabase-partitioningproc-sql
Anthony Martin
Anthony Martin

Reputation: 787

SAS : get the observation number of the max/min value of a variable

I am trying to get as a macro variable or a plain variable the number of the observation that is the min/max of one variable.

I can get easily the value of this min/max, via a proc sql

proc sql noprint;
select min(variable) into :minvariable
from have;
quit;

Or via a proc summary

proc summary data=want;
var variable;
output out=mintable min=minvariable;
run;

But I have not fouhd in either method an easy way to find the number of the observation this minimum corresponds to.

Background : I want to separate my sorted database based on this observation number, because I know that sorted in the right way there is a global minimum, and my variable of interest follows a U-curve

Upvotes: 1

Views: 851

Answers (2)

Chris J
Chris J

Reputation: 7769

You can do this in a single pass through the data, using retain. Where the same min/max value occurs multiple times, the observation of the first one seen is stored.

data want ;
  set have end=eof ;
  retain minval minobs maxval maxobs ;

  if value < minval or missing(minval) then do ;
    minval = value ;
    minobs = _n_ ;
  end ;

  if value > maxval or missing(maxval) then do ;
    maxval = value ;
    maxobs = _n_ ;
  end ;

  if eof then output ;
run ;

Upvotes: 2

kl78
kl78

Reputation: 1666

I hope i understand you correct. You want to know at which Position your minvariable is at your ordered table? Then you can just run a datastep and write the Position (which is _n_ in datastep) in a macrovariable (or data-variable if you prefer) when your minvariable is matching.

data _null_;

set have;

if variable =&minvariable then
call symputx("obsnr", _n_);
run;

This works if your minvariable is unique

Upvotes: 0

Related Questions