Multiplication of tables in SAS

[Osebeyo]

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

[DanielSantos · Accepted solution]

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

[Osebeyo]

Thanks, it worked

[Keith]

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

[DanielSantos]

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

[Patrick]

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;

[Haikuo]

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

Haikuo

[Haikuo]

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

[data_null__]

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;

[data_null__]

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_