07-18-2017 05:06 AM
Just after a hand with the below code. In addition to calculating the cumulative return over J months, I need to calculate the change the 'vol' variable over 'j' months. I keep running into problems where it refers to the wrong vol data.
Any assistance would be great.
proc sql; create table umd as select distinct a.stock, a.date, a.vol, sum(log(b.return) - log(b.mkt)) as cum_return from mom as a, mom as b where a.stock=b.stock and 0<intck('month',b.date,a.date)<(&J) group by a.stock, a.date; having count(b.return)=&J; quit;
07-18-2017 07:11 AM
Please provide sample data (data step creating such data) which ideally works with the code you've posted. Then explain what you want to do and show us how your desired output should look like (based on the sample source data).
07-18-2017 07:28 AM
proc sql; create table mom as select group_tcode as stock, datepart(last_trading_date) as date, price_relative-1 as return, mkt_prel-1 as mkt, volume_total as vol, total_capital as size from test where price_relative ^= -9 and price_relative ^= -99 and ltd_yr > (&begyear-2) and ltd_yr < (&endyear+1) group by last_trading_date; quit;
The above code creates the MOM table referenced in the previous code. The code is all based on the code referred to in this document: Get Momentum Stocks
(refer to step 3 to find where I am up to in my code)
For each respective stock and 'j' month period, the output is currently the cumulative returns. I wish to also calculate the change in trading volume over the same 'j' month period (once again differing for each stock).
07-18-2017 08:20 AM
The code in your link references existing tables that nobody here has access to, and there's no information about dataset structure of the initial tables.
Please use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset "test" into a data step for posting here (limit the number of obs so that your issues are properly illustrated).
07-18-2017 10:03 AM
infile datalines dsd truncover;
input last_trading_dateATETIME. group_tcode:$6. ltd_yr:BEST12. ltd_mo:BEST12. price_relative:BEST12. total_capital:BEST12. volume_total:BEST12. mkt_prel:BEST12.;
format last_trading_date DATETIME. ltd_yr BEST12. ltd_mo BEST12. price_relative BEST12. total_capital BEST12. volume_total BEST12. mkt_prel BEST12.;