Data Step to Find Product of Row by Column

[jawhitmire]

Windows 10, Version 9.4, no access to SAS/STATS thus cannot use "proc iml"

Good day,

Just trying to multiply a row of one dataset by the column of another.  I would prefer not to use multiple arrays.  Totally open to the possibility of using "proc sql".  However, the datasets are actually VERY LARGE and this leads to an error message about cross-referencing.  I have simplified the problem to gain insight.

This is the source code:

data work.alpha2;
                infile datalines;
                input fix @@;
                datalines;
            0 
			1 
			23 
			58 
		   ;
               run;

data work.beta2;
                infile datalines;
                input al s zn zr;
                datalines;
             5 7 8 4
               ;
run;

This is what I tried;

data work.gamma;
	merge work.alpha2 work.beta2;
	array nums {*} _numeric_;
	do _n_=2 to dim(nums);
		nums{_n_}=nums{_n_}*fix;
	end;
	drop fix;
run;

This is the result:

What I want for output in work.gamma is ....

al   s     zn     zr

0   7     184   234

Assistance is greatly appreciated.  Thank you in advance.

Jane

[novinosrin]

data work.alpha2;
                infile datalines;
                input fix @@;
                datalines;
            0 
			1 
			23 
			58 
		   ;
               run;

data work.beta2;
                infile datalines;
                input al s zn zr;
                datalines;
             5 7 8 4
               ;
run;


proc transpose data=alpha2 out=temp(drop=_name_) prefix=fix;
var fix;
run;

data want;
set beta2 ;
if _n_=1 then set temp;
array a(*) al--zr;
array b(*) fix:;
do _n_=1 to dim(a);
a(_n_)=a(_n_)*b(_n_);
end;
drop fix:;
run;

[jawhitmire]

Thank you for the reply.

For some strange reason, the use of "proc transpose" leads to no SAS errors, but inaccurate calculations.

Values in the actual data set are not integers and vary in complexity.

Thanks again,

Jane

[ballardw]

Do you have access to SAS/IML?

[jawhitmire]

No.  I do not have access to SAS/IML.

I regret not mentioning that important aspect. 

I do, however, have "proc fcmp" for multiplying matrices. 

Thanks,

Jane

[novinosrin]

data work.alpha2;
                infile datalines;
                input fix @@;
                datalines;
            0 
			1 
			23 
			58 
		   ;
               run;

data work.beta2;
                infile datalines;
                input al s zn zr;
                datalines;
             5 7 8 4
               ;
run;

data want;
if _n_=1 then do;
 dcl hash H (ordered: "A") ;
   h.definekey  ("_n_") ;
   h.definedata ("fix") ;
   h.definedone () ;
   dcl hiter hh('h');
	do _n_=1 by 1 until(lr);
		set alpha2 end=lr;
	rc=h.add();
end;
end;
set beta2;
array a(*) al--zr;
do _n_=1 by 1 while(hh.next()=0);
a(_n_)=a(_n_)*fix;
end;
drop rc fix;
run;

[PGStats · Accepted solution]

I would go for:

data gamma;
set beta2;
array _b _numeric_;
do i = 1 to dim(_b) until(done);
    set alpha2 end=done;
    _b{i} = fix * _b{i};
    end;
drop i fix;
run;

robust against mismatches in dimensions.

[jawhitmire]

Brilliant!  Thank you!

This worked without creating math miscalculations - 

For some strange reason, the use of "proc transpose" leads to no SAS errors, but inaccurate calculations.

The comment about being robust to mismatches in dimension is interesting.

Do you know where I could learn more about this?  Is there a fix within "proc transpose" that will correct the issue?

Thanks again,

Jane

[Tom]

You just want to multiple the first value of Alpha by the first value in Beta, and then second value on Alpha by the second value in Beta?

Given the way your data is structured that is simple with and ARRAY and a SET with POINT= option.

data alpha;
input fix @@;
datalines;
0 1 23 58 
;
proc print; run;

data beta;
input al s zn zr;
datalines;
5 7 8 4
1 1 1 1
;
proc print; run;

data want ;
  set beta ;
  array vars _numeric_;
  do p=1 to dim(vars);
    set alpha point=p nobs=nobs;
    vars(p)=vars(p)*fix;
  end;
  drop fix;
run;

proc print;
run;

 

[jawhitmire]

Tom,

Thank you for the reply.  

This works perfectly when the first dataset is actually the same length of each observation of the second dataset.

There is a dimension issue that I failed to mention in the original question.

Nevertheless, this reply is a keeper for my notes and use.

Thanks again,

Jane

[PaigeMiller]

PROC SCORE will do this row by column multiplication without a lot of programming.

[jawhitmire]

Oh yes, it would be wonderful to use "proc score" - but it appears it requires the SAS/STAT library.

Without this and without SAS/IML, it sometimes feels like re-inventing the wheel.

Very happy to have assistance from the SAS Community.

Thank you for the reply.

Jane

[Tom]

---

@jawhitmire wrote:

Tom,

Thank you for the reply.  

This works perfectly when the first dataset is actually the same length of each observation of the second dataset.

There is a dimension issue that I failed to mention in the original question.

Nevertheless, this reply is a keeper for my notes and use.

Thanks again,

Jane

---

Not sure how the different observations could have different dimensions.  Perhaps there is a counter variable that indicates how many of the values to use?

You can use the NOBS= option on the second SET statement to get a variable with the number of observations in ALPHA.  You could then check that to make sure your DO loop doesn't index beyond the length of ALPHA.