SAS code is taking too long

[avecesar97]

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. 

[Reeza]

DELETED as per request.

[avecesar97]

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"?

[Kurt_Bremser]

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.

[ChrisNZ]

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.