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
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
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.
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,
@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.
Hello @yellowAve,
For non-negative AB_n values you can use
AB=geomean(of AB_3-AB_5)**n(of AB_3-AB_5);
They can be negative decimals as well.
Sorry for not specifying, I've updated the original post too.
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
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.