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

Question

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

score 2 · Accepted Answer · answered Mar 16 '16 at 16:15

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 ;

score 0 · Answer 2 · answered Mar 16 '16 at 15:55

0

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

answered Mar 16 '16 at 15:55

kl78

1,628
1
16
26

I was hoping to do it in one step but it is precisely what I am loooking for. It does not seem to work though. I do not get any error when running your code, but when I try to call `%put &obsnr`, I get `WARNING: Apparent symbolic reference OBSNR not resolved.` And indeed my minvariable is likely not unique. – Anthony Martin Mar 16 '16 at 16:04
The code worked for me with some testvalues. Are you sure variable =&minvariable matches in your case? Otherwise obsnr will never be created. I guess there is a solution to solve this in one step, but i have no spontaneous idea right now. If minvariable is not unique, you have to store every match in a different variable (nobs1,nobs2,...) – kl78 Mar 16 '16 at 16:19
I have tried other ways and I am afraid I have some very close values, and maybe rounded problems... – Anthony Martin Mar 16 '16 at 16:23

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

2 Answers2