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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: