BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bd_user_10
Quartz | Level 8

Hi Everyone,

 

I have the following data:

 

isindateret
FI00090000129/02/19951.013928
FI00090000129/02/19951.007988
FI00090000129/02/19950.996195
FI00090000129/02/19951.023262
FI00090000129/02/19951.026184
FI00090000129/03/19950.98583
FI00090000129/03/19950.95632
FI00090000129/03/19951.01366
FI00090000129/03/19951.042607
FI00090000129/03/19951.001514
FI00090001037/12/20051.000492
FI00090001037/12/20050.999837
FI00090001037/12/2005

0.993036

 

and I would like to get the following output

 

isindateretret1
FI00090000129/02/19951.0261841.069102
FI00090000129/03/19951.0015140.997873
FI00090001037/12/20050.9930360.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?

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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; 

 

--
Paige Miller

View solution in original post

5 REPLIES 5
Ksharp
Super User
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;

PaigeMiller
Diamond | Level 26

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; 

 

--
Paige Miller
bd_user_10
Quartz | Level 8
Thanks for your quick response! Brilliant solution!
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

bd_user_10
Quartz | Level 8
HI RW9, your code also works fine! Thanks for the code!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1522 views
  • 3 likes
  • 4 in conversation