topic Re: Multiplying Column Unknown Number of Times in SAS Programming

Multiplying Column Unknown Number of Times

anbatch — Fri, 29 Oct 2021 20:32:35 GMT

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!

Re: Multiplying Column Unknown Number of Times

Reeza — Fri, 29 Oct 2021 21:47:43 GMT

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);

Re: Multiplying Column Unknown Number of Times

ballardw — Fri, 29 Oct 2021 22:22:35 GMT

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.

Re: Multiplying Column Unknown Number of Times

Astounding — Sat, 30 Oct 2021 03:03:19 GMT

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 &num;
         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)

Re: Multiplying Column Unknown Number of Times

Kurt_Bremser — Mon, 01 Nov 2021 13:19:18 GMT

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.

Re: Multiplying Column Unknown Number of Times

anbatch — Mon, 01 Nov 2021 14:37:08 GMT

Thank you so much! This worked perfectly!

Re: Multiplying Column Unknown Number of Times

anbatch — Mon, 01 Nov 2021 14:37:59 GMT

Thank you! This was very helpful.