topic Re: Multiply rows in one dataset by a column in another dataset without any common variables in SAS Programming

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

jawhitmire — Fri, 01 Feb 2019 16:59:57 GMT

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

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

Astounding — Fri, 01 Feb 2019 17:21:47 GMT

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.

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

PaigeMiller — Fri, 01 Feb 2019 17:39:02 GMT

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.

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

novinosrin — Fri, 01 Feb 2019 17:39:09 GMT

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;

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

Tom — Fri, 01 Feb 2019 17:42:00 GMT

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

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

jawhitmire — Fri, 01 Feb 2019 18:24:19 GMT

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

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

novinosrin — Fri, 01 Feb 2019 18:35:06 GMT

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;

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

jawhitmire — Fri, 01 Feb 2019 18:36:38 GMT

Novin

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

Beautiful example and greatly appreciated.

Thank you,

Jane

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

jawhitmire — Fri, 01 Feb 2019 18:42:42 GMT

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

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

novinosrin — Fri, 01 Feb 2019 18:55:39 GMT

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;

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

Astounding — Sat, 02 Feb 2019 04:20:15 GMT

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
;