topic Re: Multiplication of tables in SAS in SAS Procedures

Multiplication of tables in SAS

Osebeyo — Wed, 19 Sep 2012 09:06:14 GMT

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

Re: Multiplication of tables in SAS

DanielSantos — Wed, 19 Sep 2012 10:08:21 GMT

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

Re: Multiplication of tables in SAS

Patrick — Wed, 19 Sep 2012 11:14:57 GMT

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;

Re: Multiplication of tables in SAS

Osebeyo — Wed, 19 Sep 2012 11:26:15 GMT

Thanks, it worked

Re: Multiplication of tables in SAS

Keith — Wed, 19 Sep 2012 11:28:25 GMT

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

Re: Multiplication of tables in SAS

DanielSantos — Wed, 19 Sep 2012 13:45:46 GMT

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

Re: Multiplication of tables in SAS

Haikuo — Wed, 19 Sep 2012 14:20:52 GMT

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

Re: Multiplication of tables in SAS

data_null__ — Wed, 19 Sep 2012 14:33:32 GMT

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;

Re: Multiplication of tables in SAS

Haikuo — Wed, 19 Sep 2012 14:38:00 GMT

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

Haikuo

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

data_null__ — Fri, 21 Sep 2012 15:29:38 GMT

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_