BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sunless07652
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

4 REPLIES 4
art297
Opal | Level 21

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

Astounding
Opal | Level 21

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.

Ksharp
Super User

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;
sunless07652
Fluorite | Level 6

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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