Hi,
I hope someone is able to help me with below.
I have two datasets: Dataset 1 contains raw data for a number of variables and dataset 2 contains a set of weights for the same variables. Example is shown below:
Dataset 1: Raw data
A | B | C | D |
8 | 2 | 4 | 7 |
1 | 3 | 5 | 6 |
7 | 3 | 3 | 10 |
4 | 3 | 7 | 5 |
1 | 6 | 7 | 8 |
4 | 1 | 7 | 9 |
2 | 7 | 1 | 2 |
1 | 8 | 10 | 10 |
7 | 7 | 6 | 10 |
Dataset 2: weights
B | A | D | C |
0.2 | 0.3 | 0.1 | 0.4 |
I would like to multiply all observations A in dataset 1 with the weight A in dataset 2. The same goes for variables B, C and D. The variables are though not listed in the same order. Can someone please help me with this?
*** Actual dataset contains a large number of variables, so solutions without manual calculation for each variable would be greatly appreciated***
Hi,
try this:
data Dataset1;
input A B C D;
cards;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
run;
data Dataset2;
input B A D C;
cards;
0.2 0.3 0.1 0.4
;
run;
data want;
if 0 then set Dataset1; /* set vars order */
array vars A--D;
array weights[4] _temporary_;
set Dataset2;
do _I_ = lbound(vars) to hbound(vars); /* create temporary weights */
weights[_I_] = vars[_I_] ;
end;
do until(eof); /* loop over dataset */
set Dataset1 end=eof;
do _I_ = lbound(vars) to hbound(vars); /* update values */
vars[_I_] = vars[_I_] * weights[_I_];
end;
output;
end;
stop;
run;
All the best
Bart
proc sql;
create table want as
select
a * (select a from ds2) as product_a,
b * (select b from ds2) as product_b,
c * (select c from ds2) as product_c,
d * (select d from ds2) as product_d
from ds1;
quit;
Thank you for your answer.
Do you know if this can be done without writing each calculation manually? I ask because I have a dataset containing 90 variables, and manual calculation will be very time consuming.
Hi,
try this:
data Dataset1;
input A B C D;
cards;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
run;
data Dataset2;
input B A D C;
cards;
0.2 0.3 0.1 0.4
;
run;
data want;
if 0 then set Dataset1; /* set vars order */
array vars A--D;
array weights[4] _temporary_;
set Dataset2;
do _I_ = lbound(vars) to hbound(vars); /* create temporary weights */
weights[_I_] = vars[_I_] ;
end;
do until(eof); /* loop over dataset */
set Dataset1 end=eof;
do _I_ = lbound(vars) to hbound(vars); /* update values */
vars[_I_] = vars[_I_] * weights[_I_];
end;
output;
end;
stop;
run;
All the best
Bart
data one;
input A B C D;
datalines;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
data two;
input B A D C;
datalines;
0.2 0.3 0.1 0.4
;
proc sql noprint;
select name into :n separated by ' '
from dictionary.columns
where libname='WORK' and upcase(memname)=upcase('one');
quit;
data v_two / view=v_two;
format &n.;
set two;
run;
data want(drop=i j lr);
format &n.;
array v {4} &n.;
array w {4} _temporary_;
set v_two;
do i=1 to dim(w);
w[i]=v[i];
end;
do until (lr);
set one;
do j=1 to dim(v);
v[j]=v[j]*w[j];
end;
output;
end;
run;
Result:
A B C D 2.4 0.4 1.6 0.7 0.3 0.6 2 0. 6 2.1 0.6 1.2 1 1.2 0.6 2.8 0.5 0.3 1.2 2.8 0.8 1.2 0.2 2.8 0.9 0.6 1.4 0.4 0.2 0.3 1.6 4 1 2.1 1.4 2.4 1
Just for fun
data _two;
if 0 then set one nobs=nobs;
do i=1 to nobs;
set two point=_N_;
output;
end;
stop;
drop i;
run;
proc fcmp;
array _1 {9, 4} / nosymbols;
array _2 {9, 4} / nosymbols;
array want {9, 4} / nosymbols;
rc=read_array('one', _1);
rc=read_array('_two', _2);
call elemmult(_1, _2, want);
rc=write_array('want', want);
run;
Nice one,
the only sad thing is you can't do:
data _two / view = _two;
because `read_array` falls...
All the best
Bart
@yabwon, yes, seems read_arrar sadly can't handle views.
data one;
input A B C D;
datalines;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
data two;
input B A D C;
datalines;
0.2 0.3 0.1 0.4
;
proc transpose data=one(obs=0) out=name;
var _all_;
run;
proc sql;
select cats(_name_,'=_',_name_) into : rename separated by ' ' from name;
select cats(_name_,'=',_name_,'*_',_name_) into : multi separated by ';' from name;
quit;
data want ;
set one;
if _n_=1 then set two(rename=(&rename));
&multi ;
drop _:;
run;
OR if you have IML which would get you faster .
data one;
input A B C D;
datalines;
8 2 4 7
1 3 5 6
7 3 3 10
4 3 7 5
1 6 7 8
4 1 7 9
2 7 1 2
1 8 10 10
7 7 6 10
;
data two;
input B A D C;
datalines;
0.2 0.3 0.1 0.4
;
proc iml;
use one;
read all var _all_ into one[c=vname1];
close;
use two;
read all var _all_ into two[c=vname2];
close;
mattrib two c=vname2 r={'x'};
want=one#two['X',vname1];
create want from want[c=vname1];
append from want;
close;
quit;
I know it's solved, but found this interesting enough to share:
data _null_;
set dataset2;
array nums {*} _numeric_;
call execute ('data want; set dataset1;');
do k=1 to dim(nums);
formula = cats(vname(nums{k}), '=' , vname(nums{k}), '*', nums{k}, ';' ) ;
call execute(formula);
end;
call execute('run;');
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.