topic Re: Multiplying more than 100 pairs of columns in SAS Programming

Multiplying more than 100 pairs of columns

Re: Multiplying more than 100 pairs of columns

Re: Multiplying more than 100 pairs of columns

Re: Multiplying more than 100 pairs of columns

Re: Multiplying more than 100 pairs of columns

daradanye — Tue, 22 Jan 2019 03:19:39 GMT

Hi,

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

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 — Tue, 22 Jan 2019 03:35:44 GMT

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 — Tue, 22 Jan 2019 03:42:16 GMT

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 — Tue, 22 Jan 2019 09:01:14 GMT

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.

tony_coeff

mike_coeff

ballardw — Tue, 22 Jan 2019 15:37:46 GMT

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