Multiplying more than 100 pairs of columns

[daradanye]

Hi,

I meet some difficulty when I try to write SAS codes. What I have is the following dataset:

id	tony	amy	mike	tony_coeff	amy_coeff	mike_coeff
1	2	3	4	1	2	3
2	2	1	2	2	3	4
3	3	3	3	4	3	3

What I want to do is to multiply columns one by one, i.e: multiply tony with tony_coeff, multiply amy with amy_coeff:

id	tony	amy	mike	tony_coeff	amy_coeff	mike_coeff	tony_new	amy_new	mike_new
1	2	3	4	1	2	3	2	6	12
2	2	1	2	2	3	4	4	3	8
3	3	3	3	4	3	3	12	9	9

I have more than 100 pairs of like this.  I am writing a code like this:

data funda2_df4;set funda2_df3;
array vars3{0:99} tony_new-mike_new;
array vars1 tony--mike;
array vars2 tony_coeff--mike_coeff;
do _m = 1 to dim(vars3);
do _i = 1 to dim(vars1);
do _j = 1 to dim(vars2);
vars3[_m]=vars1[_i]*vars2[_j];
end;
end;
end;
run;

But it does not work.

I will be very grateful if anyone can help me!

Many Thanks! 

[Reeza]

I think you're pretty close:

data funda2_df4;
set funda2_df3;

array vars3{0:99} tony_new-mike_new;
array vars1 tony--mike;
array vars2 tony_coeff--mike_coeff;

do i = 1 to dim(vars3);

    vars3[i] = vars1[i] * vars2[i];

end;

run;

---

@daradanye wrote:

Hi,

I meet some difficulty when I try to write SAS codes. What I have is the following dataset:

id	tony	amy	mike	tony_coeff	amy_coeff	mike_coeff
1	2	3	4	1	2	3
2	2	1	2	2	3	4
3	3	3	3	4	3	3

What I want to do is to multiply columns one by one, i.e: multiply tony with tony_coeff, multiply amy with amy_coeff:

id	tony	amy	mike	tony_coeff	amy_coeff	mike_coeff	tony_new	amy_new	mike_new
1	2	3	4	1	2	3	2	6	12
2	2	1	2	2	3	4	4	3	8
3	3	3	3	4	3	3	12	9	9

I have more than 100 pairs of like this.  I am writing a code like this:

data funda2_df4;set funda2_df3;
array vars3{0:99} tony_new-mike_new;
array vars1 tony--mike;
array vars2 tony_coeff--mike_coeff;
do _m = 1 to dim(vars3);
do _i = 1 to dim(vars1);
do _j = 1 to dim(vars2);
vars3[_m]=vars1[_i]*vars2[_j];
end;
end;
end;
run;

But it does not work.

I will be very grateful if anyone can help me!

Many Thanks! 

---

[Reeza · Accepted solution]

I don't know that this type of array declaration would work either: 

array vars3{0:99} tony_new-mike_new;

You may need to manually type those out, and if so, you can use the same approach to create the avarialbe names as in my other answer:

proc sql noprint;

select catx('_', name, "new") into :var_list
from sashelp.vcolumn 
where upper(libname) = 'WORK' and upper(memname) = 'yourDataSetName'
and varnum between 3 and 103;

quit;

%put &var_list;

[s_lassen]

Instead of using SQL to fetch the variable names, and arrays to process them, you can generate the code you need, e.g.:

filename tempsas temp;

data _null_;
  set funda2_df3;
  file tempsas;
  array vars1 tony--mike;
  do _N_=1 to dim(vars1);
    name=vname(vars1(_N_));
    put name +(-1) '_new =' name '* ' name +(-1) '_coeff;';
    end;
  stop;
run;

data funda2_df4;
  set funda2_df3;
  %include tempsas;
run; 
  

(Code is not tested, as you did not submit a data step to create test data)

But is this what you want? Apparently, your own code tries to calculate every new variable as the product of every combination of the two other variables. Meaning that the end result will be that every new variable will end up containing the product MIKE*MIKE_COEFF.

[ballardw]

---

@daradanye wrote:

Hi,

I meet some difficulty when I try to write SAS codes. What I have is the following dataset:

id	tony	amy	mike	tony_coeff	amy_coeff	mike_coeff
1	2	3	4	1	2	3
2	2	1	2	2	3	4
3	3	3	3	4	3	3

---

If you are involved in a similar project in the future you may have learned a lesson about normalized data. Instead of collecting  "across" if your data had been originally entered as

ID NAME value Coef
1  Tony 2     1
1  Amy  3     2

then this would have been trivial as the multiplication would simply be value*coeff.

This would also allow for additional names without changes to multiplication code. Your current data, assuming you ever have a need to add more people, likely will require additional processes.