Multiplying Columns in Succession (i.e. multiply AB_3, AB_4, AB_5)

[yellowAve]

I have a data set:

Path	AB_1	AB_2	AB_3	AB_4	AB_5	AB_6	AB_7
1	-1.0	23	12	-2.003	12	4	2
2	2	-13.2	-3.08	1	3	-1.233	-1.6
3	13	24	2	4	-2.875	1	3

I want to set a start number and an end number (i.e. start = 3 and end = 5) such that I can get:

Path	AB = AB_3*AB_4*AB_5
1	-288.432
2	-9.24
3	-23

So then if I want to specify start multiplying from AB_4 to AB_7, the code can do it for me.

Currently I'm trying to use a %do loop but I notice that the multiplication operator (*) causes the statement to become a comment:

%macro compound_n_year(start =, end =);
	data AB(keep = path &prov.);
		retain path;
		set data_st;
		
		&prov. = (
					%do i = 0 %to %eval(&end. - &start.);
						%if &i. = 0 %then %do;
							(1+AB_%eval(&start.+&i.))
						%end;
						%else %do;
							*(1+AB_%eval(&start.+&i.))
						%end;
					%end;
				)**12;
	run;
%mend;

I'm looking into arrays, and notice that there is no "product()" function available to me. 

Is there an alternative way for me to implement the function?

I believe proc iml is not available to me.

Thank you

[ballardw]

I think that you need to provide a bit more details of what you want to do in other cases. Is this always going to be the product of 3 numbers? Will they always be sequential columns? And if you are doing multiple start/end what the output set is supposed to look like.

Since your code includes the words COMPOUND and a suspicious possible 12 month value, I might suggest providing a description of exactly what this is supposed to calculate. If it is a financial calculation you might be reinventing the wheel as there are a large number of financial functions in SAS and many of them take direct lists of variables. Which would make arrays the better approach.

[yellowAve]

I plan to specify the start column and the end column.

So I can specify 1 and 7, and the code would multiply columns 1 to 7 for me.

It can also be 4 to 6, or 3 to 50, it doesn't matter.

Yes this is applied in a financial setting,

[ballardw]

---

@yellowAve wrote:

I plan to specify the start column and the end column.

So I can specify 1 and 7, and the code would multiply columns 1 to 7 for me.

It can also be 4 to 6, or 3 to 50, it doesn't matter.

Yes this is applied in a financial setting,

---

And what financial calculation is this? The FINANCE function all by itself has 50+ calculations available. And there are another 30+ financial related functions.

[FreelanceReinh]

Hello @yellowAve,

For non-negative AB_n values you can use

AB=geomean(of AB_3-AB_5)**n(of AB_3-AB_5);

[yellowAve]

They can be negative decimals as well.

Sorry for not specifying, I've updated the original post too.

[Tom · Accepted solution]

So if you just want to use a single start/stop pair at a time then your approach should work.  Just simplify it.

%macro mult(start,end);
%local i sep;
%do i=&start %to &end;
  &sep AB_&i
  %let sep=*;
%end;
%mend mult;

Let's try it out:

options mprint;
data test;
 input ab_1 ab_2 ab_3 ;
 ab=%mult(1,3);
cards;
1 2 3
2 3 4
1 2 1
;

Result

Obs    ab_1    ab_2    ab_3    ab

 1       1       2       3      6
 2       2       3       4     24
 3       1       2       1      2

[yellowAve]

Thank you!