## Multiplication of tables in SAS

Solved
Occasional Contributor
Posts: 13

# Multiplication of tables in SAS

Hi,

I am trying to get the product of two dataset, is there an easier way of doing this in SAS or if there is a macro, that will be appreciated

The data is in this form;

dataset A (1X3 MATRIX)

COL1 COL2 COL3

4           7     9

Dataset B

COL1 COL2 COL3

5          10     12

9          12     8

16          1     7

The output should be in the form of multiplying all the rows in COL1 in dataset B by COL1 in dataset B ...

OUTPUT TABLE

COL1 COL2 COL3

20       70      108

36       84       72

64       7         63

Amos

Accepted Solutions
Solution
‎09-19-2012 06:08 AM
Super Contributor
Posts: 474

## Re: Multiplication of tables in SAS

data
RESULT;

set A (rename = (COL1-COL3=COLA1-COLA3));

drop COLA: COLB:; * drop original columns;

do until (_EOF); * iterate through B for each obs in A;

set B (rename = (COL1-COL3=COLB1-COLB3)) end=_EOF;

COL1=COLA1*COLB1;

COL2=COLA2*COLB2;

COL3=COLA3*COLB3;

output; * output result;

end;

run;

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

All Replies
Solution
‎09-19-2012 06:08 AM
Super Contributor
Posts: 474

## Re: Multiplication of tables in SAS

data
RESULT;

set A (rename = (COL1-COL3=COLA1-COLA3));

drop COLA: COLB:; * drop original columns;

do until (_EOF); * iterate through B for each obs in A;

set B (rename = (COL1-COL3=COLB1-COLB3)) end=_EOF;

COL1=COLA1*COLB1;

COL2=COLA2*COLB2;

COL3=COLA3*COLB3;

output; * output result;

end;

run;

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Occasional Contributor
Posts: 13

## Re: Multiplication of tables in SAS

Thanks, it worked

Regular Contributor
Posts: 151

## Re: Multiplication of tables in SAS

@Daniel, you can simplify your code slightly to the following.

data RESULT;

if _n_=1 then set A (rename = (COL1-COL3=COLA1-COLA3));

set B (rename = (COL1-COL3=COLB1-COLB3));

COL1=COLA1*COLB1;

COL2=COLA2*COLB2;

COL3=COLA3*COLB3;

drop COLA: COLB:; * drop original columns;

run;

Super Contributor
Posts: 474

## Re: Multiplication of tables in SAS

Thanks Keith for the contribution.

It's a good variation, should be noted though that it will only run as expected with a one row only table (A), as was actually Amos's example.

Performance wise I don't really know which is better, for the proposed scenario (1 row only table), both solutions would perform a unique complete read of both tables. The do/until condition gets evaluated before each iteration, but I believe, from my understanding the same happens for the if _N_=1.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Posts: 4,736

## Re: Multiplication of tables in SAS

data TableA;
input COL1 COL2 COL3;
datalines;
4 7 9
;
run;

data TableB;
input COL1 COL2 COL3;
datalines;
5 10 12
9 12 8
16 1 7
;
run;

proc sql;
create table want as
select
a.col1*b.col1 as col1
, a.col2*b.col2 as col2
, a.col3*b.col3 as col3
from TableA a, TableB b
;
quit;

Posts: 3,167

## Re: Multiplication of tables in SAS

Nice, Patrick. Isn't this what SQL does the best: Cartesian Products?

Haikuo

Posts: 3,167

## Re: Multiplication of tables in SAS

Care some hash()ing ?

data A;

input COL1 COL2 COL3

;

cards;

4           7     9

;

Data B;

input COL1 COL2 COL3

;

cards;

5          10     12

9          12     8

16          1     7

;

data product;

if _n_=1 then do;

if 0 then set b;

dcl hash b(dataset:'b', multidata:'y');

b.definekey(all:'y');

b.definedata(all:'y');

b.definedone();

dcl hiter hb('b');

end;

set a (rename=(col1=_col1 col2=_col2 col3=_col3));

rc=hb.first();

do rc=0 by 0 while (rc=0);

col1=col1*_col1; col2=col2*_col2; col3=col3*_col3;

output;

rc=hb.next();

end;

keep col:;

run;

Haikuo

Posts: 3,852

## Re: Multiplication of tables in SAS

SAS/Stat has a tool for this, PROC SCORE.  You have to rearange the scoring data (your data A) slightly and PROC SCORE will only create new variables.

data a;
input col1-col3;
cards;
4 7 9
run;
proc transpose data=a out=a2 prefix=P;
var col1-col3;
run;
proc transpose data=a2 out=a3;
by _name_;
id _name_;
var p1;
run;
data a3;
retain _type_ 'SCORE';

set a3;
_name_=cats('P',_name_);
run;
data b;
input col1-col3;
cards4;
5          10     12
9          12     8
16          1     7
;;;;
run;
proc score score=a3 data=b;
var col1-col3;
run;

Posts: 3,852

## Re: matching column field name to a row value and pulling in a different value from the same row

Is this reply associated with the correct subject?  No it should be associated with "How to multiply data sets thread".

Message was edited by: data _null_

🔒 This topic is solved and locked.