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!
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)
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);
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.
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)
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.
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.