How to reference data from another dataset using WHERE statement - calculating cum stock returns

[Mistletoad]

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!

[Tom · Accepted solution]

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
;