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

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

yellowAve — Wed, 28 Feb 2024 19:48:49 GMT

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_3AB_4AB_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

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

ballardw — Wed, 28 Feb 2024 18:45:16 GMT

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.

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

yellowAve — Wed, 28 Feb 2024 18:54:30 GMT

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,

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

FreelanceReinh — Wed, 28 Feb 2024 19:27:35 GMT

Hello @yellowAve,

For non-negative AB_n values you can use

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

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

yellowAve — Wed, 28 Feb 2024 19:49:48 GMT

They can be negative decimals as well.

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

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

Tom — Wed, 28 Feb 2024 20:05:06 GMT

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

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

ballardw — Wed, 28 Feb 2024 20:16:09 GMT

@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.

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

yellowAve — Wed, 28 Feb 2024 20:41:58 GMT

Thank you!