Dividing multiple columns dynamically

user_10468613 0
Viewed 209

I have a dataset that has 415 columns. 15 are computed indicators and the 400 others are numerators and denominators of indicators I want to compute. The 400 variables all have the same format i.e. *variable-name*_NUM and *variable-name*_DEN. For example, from A_NUM and A_DEN I want to compute A = divide(A_NUM, A_DEN). In other words, from the initial 415 columns, I want to end up with 15 (already computed indicators) + 200 (400/2) indicators on my data set.

At the moment I am computing them manually as follow:

data want;
    set have;

    a = divide(a_NUM,a_DEN);
    b = divide(b_NUM,b_DEN);
    c = divide(c_NUM,c_DEN);
    ...
    y = divide(y_NUM,y_DEN);
    z = divide(z_NUM,z_DEN);
    ...
run;

But I am sure there is a dynamical way of doing this (maybe using arrays?).

sas
2 Answers
data want;
set have;

array _num (*) num_:;
array _den (*) den_:;
array _results(*) results1-results200;

do i=1 to dim(_num);
  _results(i) = _num(i)/_den(i);
 end;

run;

Another option may be to transpose your data to a long structure so that you have numerator in one column and denominator in another and then do the math easily.

data long;
set have;
array _num (*) num_:;
array _den (*) den_:;


do i=1 to dim(_num);
numerator = _num(i);
denominator = _den(i);
var_num = scan(vname(_num(i)), 2, "_");
var_den = scan(vname(_den(i)), 2, "_");
output;
end;

run;

    data want;
    set have;
    length flag $8.;
    ratio = numerator/denominator;
    if var_num ne var_den then flag = "CHECKME";
    run;

    proc transpose data=want out=wide prefix=ratio_;
    by someUniqueVariable;
    id var_num ;
    var ratio;
    run;
user_19195830

This solution does not require you to rename the variables:

/* use a sql statement to generate the repeating code */
proc sql;
select trim(indicator) ||' = divide('|| trim(indicator) ||'_NUM, '|| trim(indicator) ||'_DEN)'

/* store all statements in one macro variable */
into : divisions separated by '; ';

/* but first list the indicators for which you need to do so 
  using the view SASHELP.VCOLUMN */
from (select substr(NUM.name, 1, length(NUM.name)-4) as indicator
      from sasHelp.vColumn as NUM, sasHelp.vColumn as DIV
      where NUM.libName eq 'WORK' and NUM.memName eq 'HAVE' and scan(NUM.name, -1, '_') eq 'NUM'
        and DIV.libName eq 'WORK' and DIV.memName eq 'HAVE' and scan(DIV.name, -1, '_') eq 'DIV'
        and substr(NUM.name, 1, length(NUM.name)-4) eq substr(NUM.name, 1, length(DEV.name)-4)
     )    
quit;

data WANT;
    set HAVE;
    &divisions;
run;

Note that you might need to apply the uppercase function on all column names if upper and lower case are not used consistently in column names.

user_43856470
Related