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

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

1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11


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

9 REPLIES 9
DanielSantos
Barite | Level 11


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

Osebeyo
Fluorite | Level 6

Thanks, it worked

Keith
Obsidian | Level 7

@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;

DanielSantos
Barite | Level 11

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

Patrick
Opal | Level 21

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;

Haikuo
Onyx | Level 15

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

Haikuo

Haikuo
Onyx | Level 15

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

data_null__
Jade | Level 19

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;

data_null__
Jade | Level 19

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_

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
  • 9 replies
  • 7364 views
  • 9 likes
  • 6 in conversation