Multiply rows in one dataset by a column in another dataset without any common variables

[jawhitmire]

Windows 10, Version 9.4

Good day,

The following code re-creates dataset beta whereby each value is multiplied by the corresponding column value in variable "fix" of dataset alpha:

data work.alpha;
                infile datalines;
				length nameo $5.;
                input nameo $ fix;
                datalines;
             sam 0
			 david 3
			 karen 4
			 mike 8
               ;
               run;

data work.beta;
                infile datalines;
                input sam david karen mike;
                datalines;
             5 7 8 4
			 6 4 8 9
			 7 3 2 6
               ;
run;

options symbolgen;
%let NumComp=5;
proc sql noprint;
select distinct fix into :fixit1-:fixit&NumComp 
from work.alpha
;
quit; 

data work.want;
	set beta;
	sam=sam*&fixit1;
	david=david*&fixit2;
	karen=karen*&fixit3;
	mike=mike*&fixit4;
run;

proc print data=work.want; title 'want final'; run;

The code works and produces the desired results.  However, the actual program is longer and I would like to be able to loop through "fixit1","fixit2",...,"fixit#"

Any suggestions are greatly appreciated.

Yours, Jane

[Astounding]

When you say the actual program is longer, I assume that means you have many more names.  A safer way to go would be to change the names of the macro variables you compute.  Instead of creating &FIXIT1, for example, create &SAM.  That's easily done in a DATA step:

data _null_;
                infile datalines;
				length nameo $5.;
                input nameo $ fix;
call symputx(nameo, fix);
                datalines;
             sam 0
			 david 3
			 karen 4
			 mike 8
               ;
               run;

Now you don't need to worry about the order of the names being the same in both data sets.  The idea is to replace these statements:

	sam=sam*&fixit1;
	david=david*&fixit2;
	karen=karen*&fixit3;
	mike=mike*&fixit4;

Instead, you would use the equivalent of:

	sam=sam*&sam;
	david=david*&david;
	karen=karen*&karen;
	mike=mike*&mike;

There are a few ways to generate that equivalent code.  But let's take that as a starting point and see if this approach makes sense from your point of view. 

[PaigeMiller]

PROC IML will do this type of multiplication easily.

Also, if you don't have PROC IML, you could turn the row in to a column via PROC TRANSPOSE, combine that new data set with the data set that has the column (now your data is in two columns in the same data set) and then multiplication is easy.

In either case, as long as the ordering of the values in the row is the same as the ordering of the values in the column, then you don't need to type out the names.

[jawhitmire]

Paige

Thank you for the quick reply.

Unfortunately, "proc IML" is not available.

A transpose would definitely help to merge the datasets.

Thanks again,

Jane

[novinosrin]

data work.alpha;
                infile datalines;
				length nameo $5.;
                input nameo $ fix;
                datalines;
             sam 0
			 david 3
			 karen 4
			 mike 8
               ;
               run;

data work.beta;
                infile datalines;
                input sam david karen mike;
                datalines;
             5 7 8 4
			 6 4 8 9
			 7 3 2 6
               ;
run;

proc transpose data=alpha out=_a prefix=fixit_;
var fix;
id nameo;
run;

data want_beta;
set beta;
if _n_=1 then set _a;
array t(*)sam--mike;
array j(*) fixit:;
do i=1 to dim(t);
t(i)=t(i)*j(i);
end;
drop fixit:;
run;

[jawhitmire]

Novin

The code works and avoids the need for creating so many macros.

Beautiful example and greatly appreciated.

Thank you,

Jane

[Tom · Accepted solution]

You should be able to do this with arrays.  Use PROC SQL to get the list of names and values into space delimited lists.

data alpha;
  input nameo :$32. fix @@;
datalines;
sam 0 david 3 karen 4 mike 8
;

data beta;
  input sam david karen mike @@;
datalines;
5 7 8 4 6 4 8 9 7 3 2 6
;

proc sql noprint;
select nameo,fix
  into :vars separated by ' '
     , :values separated by ' '
  from alpha
;
%let n=&sqlobs;
quit;

data want ;
  set beta ;
  array vars &vars ;
  array fix (&n) _temporary_ (&values);
  do _n_=1 to &n ;
    vars(_n_) = vars(_n_)*fix(_n_);
  end;
run;

Obs    sam    david    karen    mike

 1      0       21       32      32
 2      0       12       32      72
 3      0        9        8      48

[jawhitmire]

Tom

Thank you for the solution - it works perfectly

It is also my choice because the datasets are actually very large.  Thus, potentially using "proc transpose", as suggested by others, should perhaps be avoided as, at least in my mind and I could be totally wrong, this procedure weighs heavy on a LONG program that needs to run as quickly as possible.  

Creating a temporary array and adopting &sqlobs to monitor dimensions is ideal.

Thanks again,

Jane

[novinosrin]

Hi @jawhitmire  Hash might make it a touch faster

data work.alpha;
                infile datalines;
				length nameo $5.;
                input nameo $ fix;
                datalines;
             sam 0
			 david 3
			 karen 4
			 mike 8
               ;
               run;

data work.beta;
                infile datalines;
                input sam david karen mike;
                datalines;
             5 7 8 4
			 6 4 8 9
			 7 3 2 6
               ;
run;

data want_b;
if _n_=1 then do;
 dcl hash H (ordered: "A") ;
   h.definekey  ("_iorc_") ;
   h.definedata ("fix") ;
   h.definedone () ;
   dcl hiter hh('h');
do _iorc_=1 by 1 until(lr);
set alpha end=lr;
rc=h.add();
end;
end;
set beta;
array t(*)sam--mike;
do i=1 to dim(t);
rc=h.find(key:i);
t(i)=t(i)*fix;
end;
drop rc i nameo fix;
run;

[novinosrin]

Temporary array is a good idea but I think populating N macro vars is not needed when the PDV can hold 

data want;
do _n_=1 by 1 until(lr);
set alpha end=lr;
array t(9999)_temporary_;
t(_n_)=fix;
end;
lr=0;
do until(lr);
set beta;
array j(*) sam--mike;
do _n_=1 to dim(j);
j(_n_)=j(_n_)*t(_n_);
end;
output;
end;
keep sam--mike;
run;

[Astounding]

I know there are already several good solutions here, but there's a technique that should not be overlooked.

filename recode 'path to some file';

data _null_;
infile datalines;
input name $ fix;
file recode;
put name '=' name '*' fix ';' ;
datalines;
sam 0
david 3
karen 4 
mike 8
;

That creates a file holding all the recoding equations.  Then:

data want;
infile datalines;
input sam david karen mike;
%include recode;
datalines;
5 7 8 4
6 4 8 9
7 3 2 6
;