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.
How do you want to treat ties?
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.