BookmarkSubscribeRSS Feed
elsolo21
Fluorite | Level 6

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.

 

 

 

 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
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
Fluorite | Level 6

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

kiranv_
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 927 views
  • 1 like
  • 4 in conversation