BookmarkSubscribeRSS Feed
sfan6
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.  

5 REPLIES 5
Tom
Super User Tom
Super User

How do you want to treat ties?

sfan6
Calcite | Level 5
Use the common way. Assign the average rank of the tied values
ballardw
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;
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
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

 

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.

--
Paige Miller
StatsMan
SAS Super FREQ

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. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 375 views
  • 2 likes
  • 5 in conversation