BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mistletoad
Calcite | Level 5

Hi everyone.

The code below is to calculate cumulative returns for a security for a given time period.

 

proc sql;
create table returns as
select permno, date, ret
from crsp.dsf (keep=permno date ret)
where permno in ( 10068 , 12490 )
order by permno, date
;
create table cum_returns as
select permno , exp(sum(log(1+ret))) - 1 as cum_return
, min(ret) as minret , max(ret) as maxret
, n(ret) as n_periods , nmiss(ret) as n_miss
, sum(ret=.P) as n_dot_p , min(date) as first_date
, max(date) as last_date
from returns
where ('01jan1986'd <= date <= '31dec1986'd)
group by permno
;
quit;

 

The code above is calculating cumulative returns for 2 securities, 10068 and 12490 between the first_date and last_date (this is bolded above for clarity). 

Right now the code is written so that it calculates the cumulative returns for only these 2 securities between these two dates.

I have a separate dataset that contains a list of securities and dates that I want to calculate the cumulative returns for.

Is there a way to replace the code in the WHERE statement in which I can reference my other dataset? 

 

Below is an example of the other dataset that has this information. 

 

TransactionDate      Permnos
1	20061222	54594	
2	20060731	54594	
3	20060328	54594	
4	20060804	54594	
5	20160725	54594	
6	20060403	54594	
7	20070201	65832	
8	20070309	65832

 

Ideally, I want the transaction date to be the first date and and 6 months the transaction date after will be the last date.

 

Any help would be much appreciated.

Thank you in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Sounds like you are just asking how to join two tables in SQL.

create table cum_returns as
  select
      a.permno
    , exp(sum(log(1+a.ret))) - 1 as cum_return
    , min(a.ret) as minret
    , max(a.ret) as maxret
    , n(a.ret) as n_periods
    , nmiss(a.ret) as n_miss
    , sum(a.ret=.P) as n_dot_p
    , min(a.date) as first_date
    , max(a.date) as last_date
  from returns a
  inner join periods b
     on a.permno=b.permno 
    and a.date between b.TransactionDate and intnx('month',b.transactiondate,6,'s')
  group by a.permno
;

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

Sounds like you are just asking how to join two tables in SQL.

create table cum_returns as
  select
      a.permno
    , exp(sum(log(1+a.ret))) - 1 as cum_return
    , min(a.ret) as minret
    , max(a.ret) as maxret
    , n(a.ret) as n_periods
    , nmiss(a.ret) as n_miss
    , sum(a.ret=.P) as n_dot_p
    , min(a.date) as first_date
    , max(a.date) as last_date
  from returns a
  inner join periods b
     on a.permno=b.permno 
    and a.date between b.TransactionDate and intnx('month',b.transactiondate,6,'s')
  group by a.permno
;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 1 reply
  • 597 views
  • 0 likes
  • 2 in conversation