Combine table to multiply weight to value

[hhchenfx]

Hi Everyone,

I have 2 datasets:
1- specification contain 4 variable name (v1-v4) and it associated weight (weight1-weight4)
2- timeseries contain value of each variable name and its value overtime.

What I want is to take 1 row in specification file and multiply with all rows in timeseries.
So for the first row in specification, I will create a new dataset from timeseries where I do:

if name ='a' then value=value*0; else
if name = 'b' then value=value*0.1; else
if name = 'c' then value=value*0.1; else
if name = 'd' then value=value*0.8;

and keep only those with name a b c d.

(0, 0.1, 0.1,0.8 is the weight taken from specification file)

I will perform further analysis with this new dataset.

(This explanation of 1 row is intuition.
However, if you can help to create a file that contain all multiplication is great)

Can you please help?
Many thanks,
HHC

data specification;
input var1 $ var2 $ var3 $ var4 $ weight1 weight2 weight3 weight4 ;
datalines;
 a b c d 0 0.1 0.1 0.8
 a b v c 0.7 0.1 0.1 0.1
 ;run;

 data timeseries;
 input name $ value;
 datalines;
 a 1
 a 1.2
 a 1.3
 b 2
 b 2.1
 b 2.2
 c 3
 c 3.1
 c 3.2
 c 3.3
 d 4.1
 d 4.2
 d 4.3
 v 5.1
 v 5.2
 v 5.3
;run;

[Tom · Accepted solution]

How can A have two different weights at the same time?

Perhaps you meant to make two different sets or groups of weights?

The problem is much clearer if you use the same variable name in both datasets.  

data specification;
  input group name $ weight ;
datalines;
1 a 0
1 b 0.1
1 c 0.1
1 d 0.8
2 a 0.7
2 b 0.1
2 v 0.1
2 c 0.1
;

If you are stuck with that original unworkable layout just use a simple data step to fix it.

data good;
  set bad ;
  group+1;
  array names var1-var4;
  array weights weight1-weight4;
  do i=1 to 4;
    name=names[i];
    weight=weights[i];
    output;
  end;
  keep group name weight;
run;

Once you have something you can join/merge on then just do that.

proc sql ;
create table want as 
  select b.group,a.name,a.value,b.weight,a.value*b.weight as new_value
  from timeseries a 
    inner join specification b
    on a.name=b.name
  order by group,name
;
quit;

Results:

                                            new_
Obs    group    name    value    weight    value

  1      1       a       1.0       0.0      0.00
  2      1       a       1.3       0.0      0.00
  3      1       a       1.2       0.0      0.00
  4      1       b       2.1       0.1      0.21
  5      1       b       2.0       0.1      0.20
  6      1       b       2.2       0.1      0.22
  7      1       c       3.1       0.1      0.31
  8      1       c       3.0       0.1      0.30
  9      1       c       3.3       0.1      0.33
 10      1       c       3.2       0.1      0.32
 11      1       d       4.3       0.8      3.44
 12      1       d       4.2       0.8      3.36
 13      1       d       4.1       0.8      3.28
 14      2       a       1.3       0.7      0.91
 15      2       a       1.0       0.7      0.70
 16      2       a       1.2       0.7      0.84
 17      2       b       2.2       0.1      0.22
 18      2       b       2.1       0.1      0.21
 19      2       b       2.0       0.1      0.20
 20      2       c       3.0       0.1      0.30
 21      2       c       3.3       0.1      0.33
 22      2       c       3.2       0.1      0.32
 23      2       c       3.1       0.1      0.31
 24      2       v       5.3       0.1      0.53
 25      2       v       5.2       0.1      0.52
 26      2       v       5.1       0.1      0.51

[hhchenfx]

Thank you so much, Tom!

HHC

[Ksharp]

data specification;
input var1 $ var2 $ var3 $ var4 $ weight1 weight2 weight3 weight4 ;
datalines;
 a b c d 0 0.1 0.1 0.8
 a b v c 0.7 0.1 0.1 0.1
 ;run;

 data timeseries;
 input name $ value;
 datalines;
 a 1
 a 1.2
 a 1.3
 b 2
 b 2.1
 b 2.2
 c 3
 c 3.1
 c 3.2
 c 3.3
 d 4.1
 d 4.2
 d 4.3
 v 5.1
 v 5.2
 v 5.3
;run;

proc iml;
use specification;
read all var _char_ into char;
read all var _num_ into num;
close;
use timeseries;
read all var{name value};
close;
do i=1 to nrow(char);
 w=num[i,];c=char[i,];
 mattrib w c=c; 
 idx=loc(element(name,c)); 
 _name=_name//name[idx]; 
 _value=_value//value[idx]; 
 weight=weight//t(w[,name[idx]]);
 want=want//(  value[idx]#t(w[,name[idx]]) ); 
 group=group//j(ncol(idx),1,i);
end;
create want var{group _name _value weight want};
append;
close;
quit;