topic Re: SAS code is taking too long in SAS Programming

SAS code is taking too long

avecesar97 — Tue, 12 May 2020 21:20:55 GMT

Hi guys, I am trying to compute rolling standard deviations using a 12 month window in sas but my dataset is quite big( 4 million observations)

    date    permno  return_stock    return_mkt

01/01/2000  10000   0.05            0.04
02/01/2000  10000   0.06            0.03
03/01/2000  10000   0.03            0.08
...         
01/01/2005  10000   0.03            0.04
02/01/2005  10000   0.06            0.03
03/01/2005  10000   0.09            0.08

My code:

proc sql;
create table df1 as select *,(select std(RET) from df where permno=a.permno 
and date between intnx('month',a.date,-12) and a.date) as rolling_std_s from df as a; quit;

I have been waiting for more than 4 hours and sas is apparently still trying to execute the task. I have also created subsamples by subsetting by date and then permno but this doesn´t seem to work either. The code works fine with 5-10 stocks. I would appreciate some tips to fix this issue.

Re: SAS code is taking too long

Reeza — Wed, 13 May 2020 17:11:39 GMT

DELETED as per request.

Re: SAS code is taking too long

ChrisNZ — Tue, 12 May 2020 21:46:17 GMT

A data step would be faster, but an intermediate solution would be to use a join.

The syntax you chose runs a query for every observation of the table.

Like using the exists operator, this syntax is best avoided, and replaced by a join.

Running the 4 million queries you requested will take time of course.

Especially if the data is not indexed, which is probably true since you didn't mention this fact.

A join will benefit most from sorting the source rather than indexing.

Re: SAS code is taking too long

avecesar97 — Tue, 12 May 2020 22:43:29 GMT

Thanks! I have already handled the missing values.
data df1;
array ret{0:11} _temporary_;
set df;
by permno;
if first.permno then call missing(of ret{*});
ret{mod(_n_,12)} = monthly_ret;
std_dev = std(of ret{*});
run;
Any ideas why I´m getting this error : "The variable type of ret is invalid in this context"?

Re: SAS code is taking too long

Kurt_Bremser — Wed, 13 May 2020 06:58:01 GMT

You will do best by posting an example of your input dataset, so we can create and test code for it.

Post this example as a data step with datalines, and use the "little running man" icon to post the code.