Find the second lowest value over the past of 36 months

[sfan6]

I have around 1000 firms' monthly stock prices, and I need to find each firm's second lowest value over the past 36 months. The data looks like this:

PERMNO                           DATE                      PRICE

10001                              2011-1-31                     9.875

10001                              2011-2-28                     9.7                   

...

10001                             2022-12-31                   10.2

10002                             2011-1-31                     20.61              

...

10002                             2022-12-31                 15.65

Starting from  2011-1-31, the second lowest stock price in the 36 months would be 2014's value.  

[Tom]

How do you want to treat ties?

[sfan6]

Use the common way. Assign the average rank of the tied values

[ballardw]

What exactly does the desired output look like? A report or a data set? Is Permno the variable with the "firm" information? If not how do we know which records go with which firm?

If there is a tie for "second lowest price" do you have a rule for secondary selection criteria.

What if there is no "second lowest price" such as would occur with only one price value?

Do any records have missing values for price? Missing is "lowest" generally so would perhaps affect your meaning for "second lowest" if you don't want to include those.

A relative generic approach ignoring any secondary rules assuming you want a data set.

proc sort data=have;
   by permno price;
run;

data want;
   set have;
   by permno;
   retain counter;
   if first.permno then counter=1;
   else counter+1;
   if counter=2 then output;
   drop counter;
run;

[PaigeMiller]

This is really simple, SAS has already done the hard work for you and created PROC RANK, which will find the second lowest value

Example:

proc rank data=have out=ranks;
by permno;
var price;
ranks price_ranks;
run;

Then find the value in data set RANKS where variable price_ranks=2. This will handle ties in any one of a number of ways that you can select.

[StatsMan]

The EXTREMEOBS table in PROC UNIVARIATE is also helpful in identifying observations in the tails of your data. Use the ODS OUTPUT statement to write those observations to a SAS data set.