Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Multiplying Columns in Succession (i.e. multiply AB_3, AB_4, AB_5)

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

☑ This topic is **solved**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-28-2024 01:07 PM
(855 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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,

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hello @yellowAve,

For non-negative AB_*n* values you can use

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

They can be negative decimals as well.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thank you!

**SAS Innovate 2025** is scheduled for May 6-9 in Orlando, FL. Sign up to be **first to learn** about the agenda and registration!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

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