topic Re: Calculating Change in a Variable - Momentum Portfolios in SAS Programming

Calculating Change in a Variable - Momentum Portfolios

AMOG — Tue, 18 Jul 2017 09:06:55 GMT

Hey Guys,

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.

Cheers

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;

Re: Calculating Change in a Variable - Momentum Portfolios

Patrick — Tue, 18 Jul 2017 11:11:50 GMT

@AMOG

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).

Re: Calculating Change in a Variable - Momentum Portfolios

AMOG — Tue, 18 Jul 2017 11:28:16 GMT

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).

Re: Calculating Change in a Variable - Momentum Portfolios

Kurt_Bremser — Tue, 18 Jul 2017 12:20:32 GMT

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-data-AKA-generate/ta-p/258712 to convert your dataset "test" into a data step for posting here (limit the number of obs so that your issues are properly illustrated).

Re: Calculating Change in a Variable - Momentum Portfolios

AMOG — Tue, 18 Jul 2017 14:03:20 GMT

Apologies.

data WORK.TEST;
infile datalines dsd truncover;
input last_trading_date:DATETIME. 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.;
datalines4;
31MAY01:00:00:00,rth1,2001,5,1,266537577383,177502,1.01
31MAY01:00:00:00,mgl1,2001,5,1.01,3175569405,4099662,1.01
31MAY01:00:00:00,bsc1,2001,5,-9,201231150,0,1.01