Help using Base SAS procedures

Multiplication of tables in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

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

View solution in original post


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

Posted in reply to DanielSantos

Thanks, it worked

Regular Contributor
Posts: 151

Re: Multiplication of tables in SAS

Posted in reply to DanielSantos

@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

Respected Advisor
Posts: 4,173

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;

Respected Advisor
Posts: 3,156

Re: Multiplication of tables in SAS

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

Haikuo

Respected Advisor
Posts: 3,156

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

Respected Advisor
Posts: 3,799

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;

Respected Advisor
Posts: 3,799

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

Posted in reply to 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_

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 3135 views
  • 9 likes
  • 6 in conversation