Multiplying Column Unknown Number of Times

[anbatch]

Hello,

I have a SAS dataset that has a column that needs to be multiplied by other columns within the dataset to create a new column for each multiplication. The problem is, this needs to be repeatable and will have a different number of columns each time.

In my example, I have 3 numbers that I need my expected_volume column to be multiplied by. What I have:

What I want:

Is there any way to achieve this so that it is repeatable no matter how many value columns there are? 

So far I was thinking that I could do this with a macro like below, but I am not sure how to get it to repeat for the amount of times I need it to each time.  Is this possible to do in a do loop?

%macro repeat(num);
data matrix;
set matrix;
product&num. = expected_volume * value&num.;
run;
%mend;

%repeat(1);

Thanks for any advice!

[Reeza]

Wil you know the number of variables ahead of time, ie 3/5?

Either way, an array is probably your best choice, it's just how you define the data.
An alternative solution is to transpose your data to a long format then it doesn't matter. Do you have other columns on this table that matter?

EDIT: seems like you do know ahead of time. It can be generalized more if you'd like to either dynamically determine the number of columns or transpose to ignore that as an issue.

%macro repeat(num);
data matrix;
set matrix;

array value(&num);
array product(&num);
do i=1 to &num.;
product(i) = expected_volume * value(i).;
end;
run;
%mend;

%repeat(1);

[anbatch]

Thank you! This was very helpful.

[ballardw]

Possibly another case of instead of varying numbers of Product variables there should be Productname variable with one value holding variable. You don't describe where/what the "value" or "expected_volumn" comes from but perhaps that should be in a separate data set and combined to do the needed calculations.

[Astounding · Accepted solution]

While arrays will work, you could instead continue on the path you started.  Here's what it could look like:

%macro repeat(num);
   %local i;
   data products;
      set matrix;
      %do i=1 %to #
         product&i. = expected_volume * value&i.;
      %end;
   run;
%mend;

%repeat(1);

Also note, you do not have to feed the number of VALUE variables to the program.  You could let SAS find that out and use whatever is in the data.  For example:

data _null_;
   set matrix;
   array values {*} value: ;
   call symputx('n_values', dim(values));
   stop;
run;
%repeat (&n_values)

[anbatch]

Thank you so much! This worked perfectly!

[Kurt_Bremser]

Once again, an issue of bad data structure causing bad code.

Transpose, and it's a breeze:

data have;
input expected_volume value1 value2 value3;
datalines;
1 1 2 3
2 2 3 4
;

proc transpose
  data=have
  out=long (rename=(col1=value))
;
by expected_volume;
var value:;
run;

data want;
set long;
product = expected_volume * value;
run;

Maxim 19 : Long Beats Wide.