topic Re: Find the second lowest value over the past of 36 months in Statistical Procedures

Find the second lowest value over the past of 36 months

sfan6 — Wed, 16 Aug 2023 20:04:51 GMT

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.

Re: Find the second lowest value over the past of 36 months

Tom — Wed, 16 Aug 2023 20:12:42 GMT

How do you want to treat ties?

Re: Find the second lowest value over the past of 36 months

sfan6 — Wed, 16 Aug 2023 20:17:41 GMT

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

Re: Find the second lowest value over the past of 36 months

ballardw — Wed, 16 Aug 2023 20:23:49 GMT

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;

Re: Find the second lowest value over the past of 36 months

PaigeMiller — Wed, 16 Aug 2023 22:04:49 GMT

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.

Re: Find the second lowest value over the past of 36 months

StatsMan — Thu, 17 Aug 2023 13:14:30 GMT

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.