To determine the log differences of all possible pairs of n time series I have to calc. n!/(n-2)!2! pairs. The time series dataset comes in the form of a data set with three columns: date, TimeSeriesID, Value
I store the number of times series in the macro var nseries. then i create the pairs data set:
data pairs(keep=stock:);
do i=1 to %eval(&nseries-1);
do j=i+1 to &nseries;
ID1=i;
ID2=j;
output;
end;
end;
run;
Using SQL i create the log differences, but the log complains about not being able to optimize because of the (intentionally) created Cartesian product.
proc sql noprint;
create table logp as
select a.*
,log(b.Value/c.Value) as y
from (
select a.*
,b.date
from pairs a
,(select distinct date from TimeSeriesDS) b
)a
left join TimeSeriesDS b
on a.ID1=b.TimeSeriesID
and a.date=b.date
left join TimeSeriesDS c
on a.ID2=c.TimeSeriesID
and a.date=c.date
order by ID1,ID2,date
;
quit;
This SQL code takes some time and the output data set is huge! Is there something better I can do? (tried transposing the time series dataset, but couldn't find a way to deal with the pairs calculation)
... View more
