New Contributor
Posts: 3

Calculating Change in a Variable - Momentum Portfolios

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;``````

Posts: 4,737

Re: Calculating Change in a Variable - Momentum Portfolios

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

New Contributor
Posts: 3

Re: Calculating Change in a Variable - Momentum Portfolios

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

Super User
Posts: 10,280

Re: Calculating Change in a Variable - Momentum Portfolios

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 3

Re: Calculating Change in a Variable - Momentum Portfolios

Posted in reply to KurtBremser

Apologies.

data WORK.TEST;
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.;
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

Discussion stats
• 4 replies
• 206 views
• 0 likes
• 3 in conversation