topic Re: Need help in multiplying data! in SAS Programming

Need help in multiplying data!

bd_user_10 — Fri, 19 Oct 2018 14:06:21 GMT

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?

Re: Need help in multiplying data!

Ksharp — Fri, 19 Oct 2018 14:18:47 GMT

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;

Re: Need help in multiplying data!

PaigeMiller — Fri, 19 Oct 2018 14:21:05 GMT

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;

Re: Need help in multiplying data!

RW9 — Fri, 19 Oct 2018 14:24:21 GMT

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.

Re: Need help in multiplying data!

bd_user_10 — Fri, 19 Oct 2018 14:35:25 GMT

Thanks for your quick response! Brilliant solution!

Re: Need help in multiplying data!

bd_user_10 — Fri, 19 Oct 2018 14:40:30 GMT

HI RW9, your code also works fine! Thanks for the code!