Multiplying datasets

[elsolo21]

I have two datasets with the following format:

Dataset 1:

value1 value2 value3

   4          6         5

   5          7         3

   3          2         2

   2          4         1

Dataset 2:

Parameter   amount

   value1         .5

   value2         .1

   value3         1

I want to multiply the two datasets so the 'amount' of value1 in Dataset 2 is multiplied with each value1 in Dataset 1. Output should be:

Dataset 3:

value1   value2  value3

   2         .6            5

   2.5      .7            3 

   1.5      .2            2

   1         .4            1

Bonus add-on question:  I need to then add all the columns so row 1 would equal 7.6, etc. There are about 20 columns so I'd rather not manually build the equation.

I think this should be straightforward but I can't wrap my head around it.

[RW9]

Am not typing in test data to test this, post test data in the form of a datastep in future:

proc transpose data=ds2 out=inter prefix=calc;
   by parameter;
   var amount;
run;

data want (drop=calc:);
  merge ds1 ds2;
  array value{3};
  array calc{3};
  do i=1 to 3;
    value{i}=value{i} * calc{i};
  end;
run;

[elsolo21]

Sorry for the formatting issue but thanks for this code.  What is the 'inter' dataset?  It's not used in the merge.  Is that supposed to read 'ds2' or maybe I'm not understanding the logic.

[RW9]

Apologies, typing to quick and not testing:

proc transpose data=ds2 out=inter prefix=calc;
   by parameter;
   var amount;
run;

data want (drop=calc:);
  merge ds1 inter;
  array value{3};
  array calc{3};
  do i=1 to 3;
    value{i}=value{i} * calc{i};
  end;
run;

Basically transpose the second and merge that one result to all results in other table. 

[Ksharp]

data a;
input value1 value2 value3;
cards;
   4          6         5
   5          7         3
   3          2         2
   2          4         1
;
run;
 

data b;
input Parameter  $ amount;
cards;
   value1         .5
   value2         .1
   value3         1
;
run;

proc iml;
use a;
read all var _ALL_ into x;
close;
use b;
read all var{amount};
close;
want=(x#amount`)[,+];
create want var {want};
append;
close;
quit;

[elsolo21]

Thanks for this solution.  Unfortunately, I do not have an applicable license to run 'proc iml' so I cannot test it out.

[kiranv_]

long and not very efficient way. use this if you have few columns and few values

data a;
input value1 value2 value3;
cards;
   4          6         5
   5          7         3
   3          2         2
   2          4         1
;
run;
 

data b;
input Parameter  $ amount;
cards;
   value1         .5
   value2         .1
   value3         1
;
run;
data a1;
set a;
col =_n_;
run;
proc sql;
select coalesce(tot1,0) +coalesce(tot2,0)+coalesce(tot3,0) as final_total from 
(select  value1, col,
case when value1 and parameter = 'value1' then value1*amount end as tot1
from a1 cross join b
where calculated tot1 is not missing)x
inner join 
(select  value2,col,
case when value2 and parameter = 'value2' then value2*amount end as tot2
from a1 cross join b
where calculated tot2 is not missing)y
on x.col =y.col
inner join 
(select  value3,col,
case when value3 and parameter = 'value3' then value3*amount end as tot3
from a1 cross join b
where calculated tot3 is not missing)z
on x.col =z.col;
;