BookmarkSubscribeRSS Feed
Calcite | Level 5

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.  

Super User Tom
Super User

How do you want to treat ties?

Calcite | Level 5
Use the common way. Assign the average rank of the tied values
Super User

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;

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




Diamond | Level 26

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




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.

Paige Miller

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. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 5 in conversation