Multiply variables across datasets based on condition

[Birgithj]

Hi, 

I hope someone is able to help me with below.

I have two datasets: Dataset 1 contains raw data for a number of variables and dataset 2 contains a set of weights for the same variables. Example is shown below: 

Dataset 1: Raw data

A	B	C	D
8	2	4	7
1	3	5	6
7	3	3	10
4	3	7	5
1	6	7	8
4	1	7	9
2	7	1	2
1	8	10	10
7	7	6	10

Dataset 2: weights

B	A	D	C
0.2	0.3	0.1	0.4

I would like to multiply all observations A in dataset 1 with the weight A in dataset 2. The same goes for variables B, C and D. The variables are though not listed in the same order. Can someone please help me with this?

*** Actual dataset contains a large number of variables, so solutions without manual calculation for each variable would be greatly appreciated***

[sustagens]

proc sql;
create table want as 
select 
a * (select a from ds2) as product_a,
b * (select b from ds2) as product_b,
c * (select c from ds2) as product_c,
d * (select d from ds2) as product_d 
from ds1;
quit;

[Birgithj]

Thank you for your answer. 

Do you know if this can be done without writing each calculation manually? I ask because I have a dataset containing 90 variables, and manual calculation will be very time consuming. 

[yabwon · Accepted solution]

Hi,

try this:

data Dataset1;
input A	B	C	D;
cards;
8	2	4	7
1	3	5	6
7	3	3	10
4	3	7	5
1	6	7	8
4	1	7	9
2	7	1	2
1	8	10	10
7	7	6	10
;
run; 

data Dataset2;
input B	A	D	C;
cards;
0.2	0.3	0.1	0.4
;
run;

data want;
  if 0 then set Dataset1; /* set vars order */

  array vars A--D;
  array weights[4] _temporary_;

  set Dataset2;
  do _I_ = lbound(vars) to hbound(vars); /* create temporary weights */
    weights[_I_] = vars[_I_] ;
  end;

  do until(eof); /* loop over dataset */
    set Dataset1 end=eof;

    do _I_ = lbound(vars) to hbound(vars); /* update values */
      vars[_I_] = vars[_I_] * weights[_I_];
    end;
    output;
  end;

  stop;
run;

All the best

Bart

[PeterClemmensen]

data one;
input A B C D;
datalines;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
 
data two;
input B A D C;
datalines;
0.2 0.3 0.1 0.4
;

proc sql noprint;
    select name into :n separated by ' '
    from dictionary.columns
    where libname='WORK' and upcase(memname)=upcase('one');
quit;

data v_two / view=v_two;
    format &n.;
    set two;
run;

data want(drop=i j lr);
    format &n.;

    array v {4} &n.;
    array w {4} _temporary_;

    set v_two;
    do i=1 to dim(w);
        w[i]=v[i];
    end;

    do until (lr);
        set one;
        do j=1 to dim(v);
            v[j]=v[j]*w[j];
        end;
        output;
    end;
run;

Result:

A    B    C    D
2.4  0.4  1.6  0.7
0.3  0.6  2 0. 6
2.1  0.6  1.2  1
1.2  0.6  2.8  0.5
0.3  1.2  2.8  0.8
1.2  0.2  2.8  0.9
0.6  1.4  0.4  0.2
0.3  1.6  4    1
2.1  1.4  2.4  1

[PeterClemmensen]

Just for fun

data _two;
    if 0 then set one nobs=nobs;
    do i=1 to nobs;
        set two point=_N_;
        output;
    end;
    stop;
    drop i;
run;

proc fcmp; 
    array _1   {9, 4} / nosymbols;
    array _2   {9, 4} / nosymbols;
    array want {9, 4} / nosymbols;
    rc=read_array('one', _1);
    rc=read_array('_two', _2);
    call elemmult(_1, _2, want);
    rc=write_array('want', want);
run;

[yabwon]

Nice one,

the only sad thing is you can't do:

data _two / view = _two;

because `read_array` falls...

All the best

Bart

[PeterClemmensen]

@yabwon, yes, seems read_arrar sadly can't handle views.

[Ksharp]

data one;
input A B C D;
datalines;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
 
data two;
input B A D C;
datalines;
0.2 0.3 0.1 0.4
;
proc transpose data=one(obs=0) out=name;
var _all_;
run;
proc sql;
select cats(_name_,'=_',_name_) into : rename separated by ' ' from name;
select cats(_name_,'=',_name_,'*_',_name_) into : multi separated by ';' from name;
quit; 
data want ;
 set one;
 if _n_=1 then set two(rename=(&rename));
 &multi ;
drop _:;
run;

[Ksharp]

OR if you have IML which would get you faster .

data one;
input A B C D;
datalines;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
 
data two;
input B A D C;
datalines;
0.2 0.3 0.1 0.4
;

proc iml;
use one;
read all var _all_ into one[c=vname1];
close;

use two;
read all var _all_ into two[c=vname2];
close;

mattrib two c=vname2 r={'x'};
want=one#two['X',vname1];
create want from want[c=vname1];
append from want;
close;
quit;

[Astounding]

I know it's solved, but found this interesting enough to share:

data _null_;
   set dataset2;
   array nums {*} _numeric_;
   call execute ('data want; set dataset1;');
   do k=1 to dim(nums);
      formula = cats(vname(nums{k}), '=' , vname(nums{k}), '*', nums{k}, ';' ) ;
      call execute(formula);
   end;
   call execute('run;');
run;