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