topic Re: Multiplying Only When Field Is Populated in SAS Programming

Multiplying Only When Field Is Populated

rick_b — Sat, 17 Aug 2024 13:11:26 GMT

I am trying to find a way to multiply a series of N variables. Each line has a different number of variables present. The first field is name and the second indicates the number of times that a multiplicative variable subsequently appears:

data my_data;
input name number_observations var1 var2 var3 var4 var5;
cards;
Ed 3 2 1 4 . .
Mary 5 2 1 2 3 5
Vince 1 3 . . . .
Mike 2 4 13 . . .
;

run;

Desired output:

Ed 3 2 1 4 . . 8 (e.g., 2*1*4)
Mary 5 2 1 2 3 5 60 (e.g., 2*1*2*3*5)
Vince 1 3 . . . . 3 (e.g., 3)
Mike 2 4 13 . . . 52 (e.g., 4*13)

Re: Multiplying Only When Field Is Populated

PaigeMiller — Sat, 17 Aug 2024 13:24:59 GMT

https://blogs.sas.com/content/iml/2021/05/19/implement-product-function-sas.html

Re: Multiplying Only When Field Is Populated

Kurt_Bremser — Sat, 17 Aug 2024 13:41:11 GMT

data long;
set my_data;
array vars var:,
do i = 1 to dim(vars);
  if vars{i} ne .
  then do;
    value = vars{i};
    output;
  end;
end;
keep name value;
run;

data want;
set long;
by name;
if first.name
then product = value;
else product = product * value;
if last.name;
keep name product;
run;

Re: Multiplying Only When Field Is Populated

FreelanceReinh — Sat, 17 Aug 2024 14:15:18 GMT

Hello @rick_b,

Alternatively, you can apply the RESOLVE-%SYSEVALF trick to the variable list containing the factors:

options missing=' ';
data want;
set my_data;
if n(of var:) then prod=input(resolve('%sysevalf('||catx('*', of var:)||')'),32.);
run;
options missing='.';

(after correcting the DATA step reading the raw data).

Edit: Inserted the IF condition to catch the trivial all-missing case.

Also note the limitation of this technique (if you have thousands of factors) that the CATX(...) expression must not exceed a length of 32767 characters.

Re: Multiplying Only When Field Is Populated

rick_b — Sat, 17 Aug 2024 14:48:23 GMT

Thank you!

Re: Multiplying Only When Field Is Populated

rick_b — Sat, 17 Aug 2024 14:48:58 GMT

Thanks, Kurt.