topic Re: Multiplying datasets in SAS Programming

Multiplying datasets

elsolo21 — Thu, 08 Feb 2018 13:17:49 GMT

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.

Re: Multiplying datasets

RW9 — Thu, 08 Feb 2018 13:34:46 GMT

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;

Re: Multiplying datasets

Ksharp — Thu, 08 Feb 2018 13:58:25 GMT

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;

Re: Multiplying datasets

elsolo21 — Thu, 08 Feb 2018 14:22:15 GMT

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

Re: Multiplying datasets

elsolo21 — Thu, 08 Feb 2018 14:25:08 GMT

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.

Re: Multiplying datasets

RW9 — Thu, 08 Feb 2018 15:13:10 GMT

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.

Re: Multiplying datasets

kiranv_ — Thu, 08 Feb 2018 16:16:51 GMT

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;
;