topic Re: Data Step to Find Product of Row by Column in New SAS User

Data Step to Find Product of Row by Column

jawhitmire — Tue, 19 Mar 2019 22:49:25 GMT

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

Re: Data Step to Find Product of Row by Column

novinosrin — Tue, 19 Mar 2019 22:57:10 GMT

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;

Re: Data Step to Find Product of Row by Column

ballardw — Tue, 19 Mar 2019 23:05:59 GMT

Do you have access to SAS/IML?

Re: Data Step to Find Product of Row by Column

novinosrin — Tue, 19 Mar 2019 23:06:46 GMT


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;

Re: Data Step to Find Product of Row by Column

PGStats — Wed, 20 Mar 2019 02:38:48 GMT

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.

Re: Data Step to Find Product of Row by Column

Tom — Wed, 20 Mar 2019 02:55:22 GMT

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;

Re: Data Step to Find Product of Row by Column

jawhitmire — Thu, 21 Mar 2019 12:35:28 GMT

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

Re: Data Step to Find Product of Row by Column

jawhitmire — Thu, 21 Mar 2019 12:49:24 GMT

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

Re: Data Step to Find Product of Row by Column

jawhitmire — Thu, 21 Mar 2019 12:52:14 GMT

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

Re: Data Step to Find Product of Row by Column

jawhitmire — Thu, 21 Mar 2019 13:03:34 GMT

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

Re: Data Step to Find Product of Row by Column

PaigeMiller — Thu, 21 Mar 2019 13:05:40 GMT

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

Re: Data Step to Find Product of Row by Column

Tom — Thu, 21 Mar 2019 13:26:05 GMT

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

Re: Data Step to Find Product of Row by Column

jawhitmire — Thu, 21 Mar 2019 13:26:25 GMT

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