topic How do I multiple a column in one table by a scalar value from another table? in SAS Programming

How do I multiple a column in one table by a scalar value from another table?

bkr9000 — Tue, 07 Feb 2023 02:11:45 GMT

Hello, I'm brand new to SAS and am struggling with syntax.

I have two datasets: one 100x5 table called QUANTITIES, and one 2x12 table called COEFFICIENTS.

I need to multiply all values in QUANTITIES column called "Weight" by the scalar value found in the COEFFICIENTS column called "Wheat".

I'm able to manipulate QUANTITIES as long as I don't have to bring in any values from other datasets. For the life of me, I can't figure out how to pull in a number from COEFFICIENTS.

data work.QUANTITIES; 
   input Name $ Weight Density Units@@; 
datalines; 
   Prod1  69.0 112.5 14  
   Prod2   62.8 102.5 14 
   Prod3    59.8  84.5 12  
   Prod4    59.0  99.5 12   
; 


data work.COEFFICIENTS; 
   input Copper Iron Wheat Barley Glass Water@@; 
datalines; 
   11 22 33 44 55 66 
;

Thanks in advance

Re: How do I multiple a column in one table by a scalar value from another table?

ballardw — Tue, 07 Feb 2023 05:38:48 GMT

One way to work with ALL records of two data sets combined is in Proc SQL with a Cartesian join.

proc sql;
   create table want as
   select a.*,b.wheat, a.weight*b.wheat as product
   from work.QUANTITIES as a, work.coefficients as b
   ;
quit;

The above code has all variables from the quantities set and the value of Wheat from the coefficients set plus a product of weight*wheat (you didn't provide a result variable name). the values of weight and wheat are shown to show which values are used but they would not necessarily have to be Selected to be in the set. The A.* is "bring in all the variables from set A" and the A and B are alias to represent the set names.

Re: How do I multiple a column in one table by a scalar value from another table?

bkr9000 — Tue, 07 Feb 2023 20:11:18 GMT

Excellent, thank you for the solution and explanation! This is exactly what I was looking for.