topic Re: Getting trading days around an event date in SAS Programming

Getting trading days around an event date

somebody — Mon, 04 Nov 2019 06:32:43 GMT

I am doing an event study. So I would like to get 50 trading days before and 50 trading days after the event day. I have a dataset that contains trading dates because I cannot simply use 'weekday' for the INTNX function in SAS.

My code is :

options intervalds=(tradingdays=tradingdays) ;
	data data_window(rename=(date=date_t0 date2=date)); 
		set data_in;
		by stock date;
		if first.date;
		format date2 YYMMDD10.;
		do t=-50 to 50; date2=intnx('tradingdays',date,t) ;output; end;
	run;

Data_in contains 2 variables: stock and date which is the event date.

This runs very slow, as compared to using intnx('weekday',date,t). Is there a different way to improve the speed?

Re: Getting trading days around an event date

PeterClemmensen — Mon, 04 Nov 2019 06:34:13 GMT

@somebody can you provide an example of what data_in looks like and your desired result?

Re: Getting trading days around an event date

somebody — Mon, 04 Nov 2019 07:49:03 GMT

Here are the datasets.

Data_in the input

data_wanted is the desired output.

tradingdays contains trading days.

Take 2014-05-31 for example. its a public holiday so it is not a trading day. If I use 'weekday', SAS will return this day in my output which is not what I want. Thus, I have a dataset that contains valid trading days.

Thanks

Re: Getting trading days around an event date

Patrick — Mon, 04 Nov 2019 08:55:43 GMT

@somebody

If you know already what you would be doing if this were about weekdays then the only thing you're still missing is how to create and use a custom interval.

The data set "tradingdays" is already in a perfect format so things become really simple.

options intervalds=(Cust_TradingDays=TradingDays);
data test;
  format dt dt_plus10tradingDays dt_plus10weekDays weekdate.; 
  do dt='10dec2017'd to '20jan2018'd;
    dt_plus10tradingDays=intnx('Cust_TradingDays',dt,10);
    dt_plus10weekDays=intnx('weekdays17w.',dt,10);
    output;
  end;
  stop;
run;
proc print;
run;

Re: Getting trading days around an event date

s_lassen — Mon, 04 Nov 2019 08:50:25 GMT

I am not quit sure how the INTNX function works internally with interval tables, but I think it may be a dynamic WHERE clause. In which case it may be possible to speed things up by putting an index on the BEGIN variable.

Another possibility is to hardcode the interval fetching, e.g.:

data trading_days;
  set tradingdays;
  rename begin=date2;
  format begin YYMMDD10.;
  obs=_N_;
run;

proc sql;
  create index date2 on trading_days(date2);
run;

data data_window(rename=(date=date_t0 date2=date));
  set data_in;
  by stock date;
  if first.date;
  date2=date;
  set trading_days key=date2/unique nobs=nobs;
  do _N_=(max(obs-50,0) to min(obs+50,nobs);
    set trading_days pont=_N_;
    output;
    end;
  drop obs;
run;

Only make sure that the TRADINGDAYS data set is sorted before doing this.

Re: Getting trading days around an event date

somebody — Mon, 04 Nov 2019 10:09:26 GMT

I am not sure what I am missing. I know that using the custom trading days and weekday give different results. My questions is when I use the custom trading days, it takes a long time to create new dates.