Filtering data by date for different stocks under the same variable

[gotchj1]

I'm trying to clean up a large dataset with many stocks. The current dataset i have contains every stock that has ever existed and im trying to cut it down to all the stocks that have ever been in the S&P 500. I have a date column, a ticker column, a closing price column. Not all the stocks in the S&P 500 were added to the index at the same time and some of the stocks were delisted over time. I have currently have all of the historical data on each stock in the S&P 500. For example, Apple was added into the S&P 500 on Nov 30, 1982, but i have data for Apple going back to 1980. Im trying to write a code that doesn't delete the other stock information besides the information from 1980 to Nov 29, 1982 just for Apple. So for example, there will still be information for Amazon from 1970 to the current time, but for Apple the data will start at Nov 30, 1982.

So far i was thinking some thing along the lines of this code but it isnt working.

DATA CRSP1;
    SET CRSP;
    if Ticker='A' then "10Apr1995"d =< date => "05Jun2000"d;
    else if Ticker='AAPL' then date=>"23Mar2005"d;
    else if Ticker='AMZN' then date=>"08Dec2012"d;
run;

[HarrySnart]

Hi @gotchj1 , have you tried doing this as a join in PROC SQL? If you have a reference table with date added and stock name you could try an inner join on your main table where stock = stock and date ge date_added.

[ballardw · Accepted solution]

---

@gotchj1 wrote:

I'm trying to clean up a large dataset with many stocks. The current dataset i have contains every stock that has ever existed and im trying to cut it down to all the stocks that have ever been in the S&P 500. I have a date column, a ticker column, a closing price column. Not all the stocks in the S&P 500 were added to the index at the same time and some of the stocks were delisted over time. I have currently have all of the historical data on each stock in the S&P 500. For example, Apple was added into the S&P 500 on Nov 30, 1982, but i have data for Apple going back to 1980. Im trying to write a code that doesn't delete the other stock information besides the information from 1980 to Nov 29, 1982 just for Apple. So for example, there will still be information for Amazon from 1970 to the current time, but for Apple the data will start at Nov 30, 1982.

So far i was thinking some thing along the lines of this code but it isnt working.

DATA CRSP1;
    SET CRSP;
    if Ticker='A' then "10Apr1995"d =< date => "05Jun2000"d;
    else if Ticker='AAPL' then date=>"23Mar2005"d;
    else if Ticker='AMZN' then date=>"08Dec2012"d;
run;

---

The way youo are using "then" above you are attempting to create a value and likely throwing an error or two.

If you want to select observation the code for the above would look like this to use a subsetting IF

DATA CRSP1;
    SET CRSP;
    if (Ticker='A' and (( "10Apr1995"d =< date) or (date => "05Jun2000"d))
    or ( Ticker='AAPL' and date=>"23Mar2005"d)
    or ( Ticker='AMZN' and date=>"08Dec2012"d);
run;

Note that your ""10Apr1995"d =< date => "05Jun2000"d" is probably framed incorrectly and my guess of what you may have intended is just an example of.  The only time that condition would be true is if date => '05Jun2000'd and so includes Apr 1995.

If you intended dates between then the code would be "10Apr1995"d =< date <= "05Jun2000"d.

However the suggestion by @HarrySnart is really a much more flexible, simpler to code and likely more robust in the long run.

[mkeintz]

I suspect you'll find the proc sql suggestion most easily employed.

But, assuming the stock trading data (dataset STOCK) has variables ticker, date, and other variable of interest, ... and the SP500_LIST dataset has variablea TICKER, ENTRY_DATE, and  EXIT_DATE, then the code below should work.

data want;
  set sp500_list (in=insp500 keep=ticker entry_date rename=(entry_date=date))
      stocks (in=indaily) ;
  by ticker date ;
  if first.ticker then call missing(entry_date, exit_date);
  if insp500 then set sp500_list;  /* Retrieve entry_date and exit_date for this ticker*/
  if indaily=1 and entry_date <= date <=exit_date ;
run;

Note a ticker can enter and exit the S&P500 more than once, either because of changing finances of the company, or because TICKER has been re-assigned to different companies.  So SP500_LIST can have multiple obs per ticker.

The above code assumes:

1. STOCKS file is sorted by ticker/date.
2. SP500_LIST file is sorted by ticker/entry_date