Exclude small Penny stocks

[student12345678]

Hello,

I have downloaded the CRSP daily return data from WRDS. Now I want to exclude all small penny stocks. Therefore I want to exclude all stocks with a closing price or bid/ask average (PRC) between -5 and 5 on the 15.01.2021 (ddmmyy10.). So the stocks which have a price between -5 and 5 on the 15.01.2021 should be completely deleted from the dataset, so also on all other dates. Can someone please help me with the SAS Studio on demand for academics code?

Thanks a lot!

[sbxkoenk · Accepted solution]

Hello,

As you haven't got any answer in 4 hours, I will try to answer.

The code below was built "blindly" as you have not provided any sample data.

data work.small_penny_stocks_tobe_leftout;
 set work.download_CRSP_daily_return_WRDS;
 where date='15JAN2021'd;
 where also price between -5 and 5;
run;

PROC SQL noprint;
 create table work.want as
 select *
 from work.download_CRSP_daily_return_WRDS
 where stock_id NOT IN
  (select distinct STOCK_ID from work.small_penny_stocks_tobe_leftout)
;
QUIT;
/* end of program */

Good luck,

Koen

[student12345678]

Thanks a lot!

[Kurt_Bremser]

Similar, using a hash object:

data want;
set have;
if _n_ = 1
then do;
  declare hash ex (dataset:"have (where=(date = '15jan2021'd and -5 le prc le 5))");
  ex.definekey("stock");
  ex.definedone();
end;
if ex.check() ne 0;
run;

ex.check() ne 0 means that the key was not found in the hash. Adapt the variable names to what you have in your dataset.

[sbxkoenk]

Using a hash object table look-up might be worthwhile indeed as I know these daily datasets with closing prices and all bid/ask operations can grow very big (especially if concatenated).

Hash object table look-up happens in-memory (instead of on-disk) and is therefore much faster.

Cheers,

Koen

[ballardw]

I don't use CRSP data or WRDS.

However I might suggest that if you want to exclude stocks based on a single day set of values that you make a list of the identification variable(s) so that you can consistently exclude the same stocks for data on other days.

Since you are referencing a single day and specific range of values what happens if you look at data a week later and one or more of those stocks have values outside the specific range? If you want to be consistent in an analysis you may need to exclude them but range will no longer suffice.

Or possibly pull data from a longer range of dates to build an exclusion set.