topic Multiplication of tables (large) in SAS Procedures

Multiplication of tables (large)

sunless07652 — Wed, 05 Jul 2017 16:06:17 GMT

Hi, I'm trying to do something similar to what this question did.

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

The solution given was:

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;

However this is more difficult for me because I have many columns I want to multiply. Is there a way to do this more efficiently than just writing out the formula for each newly computed column? Also, the columns from my two tables have exactly the same column names and are listed in the same order, but don't have names like COL1, COL2,... COL100, and have different names like CANH, CANVH, etc.

Thanks.

Re: Multiplication of tables (large)

art297 — Wed, 05 Jul 2017 17:02:55 GMT

Here is one way:

data A;
  input CANH CANVH SOMETHING_ELSE;
  cards;
4           7     9
;

Data B;
  input CANH CANVH SOMETHING_ELSE;
  cards;
5          10     12
9          12     8
16          1     7
;

proc sql noprint;
  select catt(name,'=_',name),
         catt('_',name)
    into :renames separated by ' ',
         :array separated by ' '
      from dictionary.columns
        where libname=upcase('work') and
              memname=upcase('A')
          order by varnum
  ;
quit;

data want (drop=_:);
  if _n_ eq 1 then set a (rename=(&renames.));
  set b;
  array a(*)  &array.;
  array b(*) CANH--SOMETHING_ELSE;
  do _n_=1 to dim(b);
    b(_n_)=b(_n_)*a(_n_);
  end;
run;

Art, CEO, AnalystFinder.com

Re: Multiplication of tables (large)

Astounding — Wed, 05 Jul 2017 18:15:31 GMT

I would suggest skipping all the renames. Although it might be possible to come up with a variable list instead of spelling out all the names, let's assume for now that you are going to spell them out once.

data want;

if _n_=1 then do;

array vars {100} put the list of 100 names here;

array coeffs {100} _temporary_;

set a;

do _i_ = 1 to 100;

coeffs{_i_} = vars{_i_};

end;

set b;

do _i_ = 1 to 100;

vars{_i_} = vars{_i_} * coeffs{_i_};

end;

drop _i_;

run;

Copy from the data set A to a temporary array. Temporary array values are automatically retained, and don't require dropping since they are not variables. Then the multiplication is straightforward.

Re: Multiplication of tables (large)

Ksharp — Thu, 06 Jul 2017 12:42:24 GMT

It is best for IML code.

data A;
  input CANH CANVH SOMETHING_ELSE;
  cards;
4           7     9
;

Data B;
  input CANH CANVH SOMETHING_ELSE;
  cards;
5          10     12
9          12     8
16          1     7
;

proc iml;
use a;
read all var _all_ into x;
close;
use b;
read all var _all_ into y;
close;

z=x#y;

create want from z;
append from z;
close;
quit;

Re: Multiplication of tables (large)

sunless07652 — Thu, 06 Jul 2017 13:41:17 GMT

Thanks, that looks great but unfortunately my version of SAS EG doesn't have SML.