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
PROC Star

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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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