BookmarkSubscribeRSS Feed
avecesar97
Fluorite | Level 6

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. 

4 REPLIES 4
Reeza
Super User

DELETED as per request.

avecesar97
Fluorite | Level 6

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
Super User

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
Tourmaline | Level 20

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2212 views
  • 2 likes
  • 4 in conversation