SAS--how to calculate return from price data?

Question

I am using SAS. I have a dataset called EOD_close_rtn, which looks as below:

date        CF888   A9888   AG888   AL888   AU888   C9888   CU888   ER888   IF888   J9888
4-Jun-13    20220   4828    4621    14855   281.11  2434    53060   2628    2561.4  1489
5-Jun-13    20210   4827    4620    14850   281.1   2435    53080   2627    2561.6  1489
6-Jun-13    20215   4829    4621    14860   281.14  2435    53080   2627    2561.4  1491

The first column is the date, and the rest columns are prices corresponding to the contract names on top. I want to calculate the daily return for each contract, and the output should be in the same format. I tried to use LAG function, but don't know how to apply the same functions to all columns.

Your question is ambiguous, could you add a view of what your output data should look like, based on the example input data you provided. — Longfish

Joe Joe · Accepted Answer · 2013-06-10T13:46:48

If you just want the rolling difference, then you can do it with lag and arrays. Standard caveats with LAG apply; LAG does not retrieve the previous record, it instead creates a queue and adds/pops each time it's called, so do not use it in a conditional statement (like IF) unless you know what you're doing.

data have;
input date :date9. CF888   A9888   AG888   AL888   AU888   C9888   CU888   ER888   IF888   J9888;
format date date9.;
datalines;
04Jun2013    20220   4828    4621    14855   281.11  2434    53060   2628    2561.4  1489
05Jun2013    20210   4827    4620    14850   281.1   2435    53080   2627    2561.6  1489
06Jun2013    20215   4829    4621    14860   281.14  2435    53080   2627    2561.4  1491
;;;;
run;

data want;
set have;
array contracts cf888--j9888;
do _t = 1 to dim(contracts);
  contracts[_t] = contracts[_t] - lag(contracts[_t]);
end;
run;

I'd also consider changing your data structure; this sort of thing is much easier to calculate if you have a vertical structure, ie, one row per contract per date instead of one row per date.

SAS--how to calculate return from price data?

1 Answers