BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
yellowAve
Calcite | Level 5

I have a data set:

Path

AB_1AB_2AB_3AB_4AB_5AB_6AB_7
1-1.02312-2.0031242
22-13.2-3.0813-1.233-1.6
3132424-2.87513

 

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
Tom
Super User Tom
Super User

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

View solution in original post

7 REPLIES 7
ballardw
Super User

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
Calcite | Level 5

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
Super User

@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
Jade | Level 19

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
Calcite | Level 5

They can be negative decimals as well.

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

Tom
Super User Tom
Super User

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: Save the Date

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

Save the date!

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 7 replies
  • 856 views
  • 1 like
  • 4 in conversation