Hi Everyone,
I have the following data:
isin | date | ret |
FI0009000012 | 9/02/1995 | 1.013928 |
FI0009000012 | 9/02/1995 | 1.007988 |
FI0009000012 | 9/02/1995 | 0.996195 |
FI0009000012 | 9/02/1995 | 1.023262 |
FI0009000012 | 9/02/1995 | 1.026184 |
FI0009000012 | 9/03/1995 | 0.98583 |
FI0009000012 | 9/03/1995 | 0.95632 |
FI0009000012 | 9/03/1995 | 1.01366 |
FI0009000012 | 9/03/1995 | 1.042607 |
FI0009000012 | 9/03/1995 | 1.001514 |
FI0009000103 | 7/12/2005 | 1.000492 |
FI0009000103 | 7/12/2005 | 0.999837 |
FI0009000103 | 7/12/2005 | 0.993036 |
and I would like to get the following output
isin | date | ret | ret1 |
FI0009000012 | 9/02/1995 | 1.026184 | 1.069102 |
FI0009000012 | 9/03/1995 | 1.001514 | 0.997873 |
FI0009000103 | 7/12/2005 | 0.993036 | 0.993362 |
where ret1 is calculated based on isin and date. For each isin and date, I need to multiply all relevant ret.
For example; the first ret1 for isin FI0009000012 with date 9/02/1995 is calculated as following:
ret1 = 1.013928*1.007988*0.996195*1.023262*1.026184 = 1.069102
I have many firms in the dataset. Can anyone please help?
PROC SUMMARY is very good a doing sums (and means and other statistics), by each firm/date combination. But it doesn't do products 😞
So the trick is to take the logarithm of RET, then have PROC SUMMARY add up all the log(RET) values, and then take the results and un-log them. This works because adding logs and then un-logging the sum is the same as multiplying the original values.
data have2;
set have;
log_ret=log(ret);
run;
proc summary nway data=have2;
class isin date;
var log_ret;
output out=sums sum=log_ret_sum;
run;
data want;
set sums;
ret1=exp(log_ret_sum);
run;
data have;
infile cards expandtabs;
input isin : $40. date : mmddyy12. ret;
format date mmddyy10.;
cards;
FI0009000012 9/02/1995 1.013928
FI0009000012 9/02/1995 1.007988
FI0009000012 9/02/1995 0.996195
FI0009000012 9/02/1995 1.023262
FI0009000012 9/02/1995 1.026184
FI0009000012 9/03/1995 0.98583
FI0009000012 9/03/1995 0.95632
FI0009000012 9/03/1995 1.01366
FI0009000012 9/03/1995 1.042607
FI0009000012 9/03/1995 1.001514
FI0009000103 7/12/2005 1.000492
FI0009000103 7/12/2005 0.999837
FI0009000103 7/12/2005 0.993036
;
data want;
set have;
by isin date;
retain ret1;
if first.date then ret1=1;
ret1=ret1*ret;
if last.date;
run;
proc print;run;
PROC SUMMARY is very good a doing sums (and means and other statistics), by each firm/date combination. But it doesn't do products 😞
So the trick is to take the logarithm of RET, then have PROC SUMMARY add up all the log(RET) values, and then take the results and un-log them. This works because adding logs and then un-logging the sum is the same as multiplying the original values.
data have2;
set have;
log_ret=log(ret);
run;
proc summary nway data=have2;
class isin date;
var log_ret;
output out=sums sum=log_ret_sum;
run;
data want;
set sums;
ret1=exp(log_ret_sum);
run;
You can retain the previous value on a by grouped dataset:
data want; set have; by isin date; retain res; if first.date then res=ret; else res=res*ret; if last.date then output; run;
Or something on those lines.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.