BookmarkSubscribeRSS Feed
AMOG
Calcite | Level 5

 

 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;

 

4 REPLIES 4
Patrick
Opal | Level 21

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

AMOG
Calcite | Level 5
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).

 

 

 

Kurt_Bremser
Super User

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

AMOG
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 707 views
  • 0 likes
  • 3 in conversation