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
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
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
Thanks, it worked
@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;
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
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;
Nice, Patrick. Isn't this what SQL does the best: Cartesian Products?
Haikuo
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
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;
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_
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.