Looking for more efficient SAS programming code.

[JKCho]

data bmt5r;
set bmt5;
format Filing_Date yymmddn8.; 
run;
data bmt5rr; /*Making connecting identifiers*/
set bmt5r;
d_10 = intnx('day',Filing_Date,-10); 
d_9 = intnx('day',Filing_Date,-9); 
d_8 = intnx('day',Filing_Date,-8); 
d_7 = intnx('day',Filing_Date,-7); 
d_6 = intnx('day',Filing_Date,-6); 
d_5 = intnx('day',Filing_Date,-5); 
d_4 = intnx('day',Filing_Date,-4); 
d_3 = intnx('day',Filing_Date,-3); 
d_2 = intnx('day',Filing_Date,-2); 
d_1 = intnx('day',Filing_Date,-1); 
d_0 = intnx('day',Filing_Date,0); 
d1 = intnx('day',Filing_Date,1); 
d2 = intnx('day',Filing_Date,2); 
d3 = intnx('day',Filing_Date,3); 
d4 = intnx('day',Filing_Date,4); 
d5 = intnx('day',Filing_Date,5); 
d6 = intnx('day',Filing_Date,6); 
run;
proc sql; /*Merging by connecting identifiersto obtain PRICE from CRSP*/
create table d_10 as
select a.* , b.*
from CRSP1 a
inner join bmt5rr b
on a.crspcusip6 = b.cusip6 and a.DATE - b.d_10 >=0
having (a.DATE - b.d_10)=min(a.DATE - b.d_10);
quit;
proc sql; /*Merging by connecting identifiers to obtain PRICE from CRSP*/
create table d_9 as
select a.* , b.*
from CRSP1 a
inner join bmt5rr b
on a.crspcusip6 = b.cusip6 and a.DATE - b.d_9 >=0
having (a.DATE - b.d_9)=min(a.DATE - b.d_9);
quit;

Hi All,

I need -10 to +60 interval from specific dates(Filling_Date), and try to obtain daily stock price returns' standard deviations using different intervals. However, my SAS code is so inefficient.

1) Is there other codes to have '-10 to +60 day's interval at once other than specifying -10, -9 to +60 each? What I did is to need to create more than 70 separate data files and these are... hassles.

2)If I have price information from -10days of Filing_Date to +60 days, How can I get variances or standard deviations? I know SAS code calculating these but it only works on a single variable but not across variables.

So long as I follow the lengthy code above, I will have D-10 to D60 and these match with Price_10 to Price60, and finally, Return_9 to Return_60 from the simple calculation price_10/Price_9 and so on. 

SO, I can get returns anyway. BUT I do not have any clue to calculate Var or STD across returns. 

These are quite tough... I may need other codes. 

Please share your better ideas!!

Thank you!!

Ah...there should be some missing values as d_# cannot be matched with DATE(CRSP trading dates).

I did not put the code that solves this issue and this issue is not my worry at all. 

[Tom]

It is not at all clear to me how creating those D... variables help you with your actual problem.

Do you just want to multiple the size of your dataset by 16?  Or do you want to calculate 16 different summaries?

If you want to calculate summaries what variable are you taking the summary of?

How do you want to summarize it?  Do you want the min, max, mean, range? Something else.

Provide a (SMALL) example of the input data and what output you want for that example input.

[JKCho]

data bmt5r;
set bmt5;
format Filing_Date yymmddn8.; 
run;
data bmt5rr; /*Making connecting identifiers*/
set bmt5r;
d_10 = intnx('day',Filing_Date,-10); 
run;
proc sql; /*Merging by connecting identifiersto obtain PRICE from CRSP*/
create table d_10 as
select a.* , b.*
from CRSP1 a
inner join bmt5rr b
on a.crspcusip6 = b.cusip6 and a.DATE - b.d_10 >=0
having (a.DATE - b.d_10)=min(a.DATE - b.d_10);
quit;

The above code is for obtaining the price of -10days of the filling date of firms. I need this kind of price information from -10 days to +60 days as I did above. To do so, I need to make almost identical 71(from -10 to +60) codes, which I do not want to do.

Those Ds are dates and identifiers.

What I am doing now is to obtain stock prices throughout the matching process by date(those Ds) and Cusip(a unique # for each firm).

Since I need stock prices from -10 days to +60 days of each filling date of each firm, I  created those D variables to simply match the price of -10day, price of -9 days and all the way to +60 days of the filing date.

What I want is to obtain prices of Those D dates by using much simpler code(s).

I don't want summaries and rather want to kind of merge. 

[Tom]

What analysis are doing over the different time periods?

The most efficient solution will probably be one that does not require that you replicate the data 70 times as the first step.

Do you have access to PROC ETS?

[JKCho]

Thank you for your reply, Tom!

My ultimate goal is to obtain stock return volatility. 

What I did first was getting prices by those many D dates so I could get a price on each date from -10 to +60.

Then I can calculate returns over these prices, average returns over these 70 days, and finally stock return volatilities.

Maybe, to make a long story short, I want stock return volatilities over the "-10 to +60" period. Later I will regress these volatilities on my measure so that I can say the relation between them.

I did not know what PROC ETS is and searched for it. However, still not sure what it is... does not look like a regression command...

[ballardw]

I believe that @Tom actually meant to say SAS/ETS , a module that contains several procedures for working with time series data.

You would find out if you have a license by running

Proc Setinit;
run;

If licensed SAS/ETS should appear in the log output for the procedure.

[ChrisNZ · Accepted solution]

You need to move to an equijoin to speed up the performance and avoid a Cartesian product.

One way to do this is shown here and involves creating an intermediate table with the desired matching values (in your case the different possible number of days of difference). So the intermediate table just holds one field DIF and records with values -10, -9, etc. You can then have an equijoin on TABLE.DATE-TABLE2.DATE=TABLE3.DIF.