Help using Base SAS procedures

Multiplication of tables (large)

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Multiplication of tables (large)

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.


Accepted Solutions
Solution
‎07-05-2017 04:58 PM
PROC Star
Posts: 7,474

Re: Multiplication of tables (large)

[ Edited ]
Posted in reply to sunless07652

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

View solution in original post


All Replies
Solution
‎07-05-2017 04:58 PM
PROC Star
Posts: 7,474

Re: Multiplication of tables (large)

[ Edited ]
Posted in reply to sunless07652

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

Super User
Posts: 5,509

Re: Multiplication of tables (large)

Posted in reply to sunless07652

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;

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.

Super User
Posts: 10,035

Re: Multiplication of tables (large)

Posted in reply to sunless07652

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;
New Contributor
Posts: 2

Re: Multiplication of tables (large)

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

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 208 views
  • 2 likes
  • 4 in conversation