topic Average of a partial array with no common prefix in SAS Enterprise Guide

Average of a partial array with no common prefix

DDawaba — Wed, 29 Mar 2023 11:40:24 GMT

The first "M" array gets the average of all numeric variables in the dataset. There's a total of 74 variables: X,Y,Z,V,P,L...etc

However, I would like to get the average of only that last 6 variables, i.e. from 69 - 74th variable. I tried creating another array "L" where I identified the do statement to start from (i=69 to i+5). The average function does not work still. Any help?

data XXX;
Set YYYY;
array M(*) _numeric_;
do i=1 to dim(M);
TOTAVG=mean(of M(*));
drop i;
end;

array L(*) _numeric_;
i=69;
do n=i to i+5;
AVG2=mean(of L[n]);
end;

run;

Re: Average of a partial array with no common prefix

SASJedi — Wed, 29 Mar 2023 12:41:05 GMT

The first average does not require a DO loop - you can just use of M[*] and the list of values. This code produces the average of all numeric variables as TOTAVG, and the average of the right-most 2 variables as Last2Avg:

data want;
	set sashelp.fish(obs=3);
	array M(*) _numeric_;
	TOTAVG=mean(of M(*));
	do _i=dim(m)-1 to dim(m);
		_sum=sum(_sum,m[_i]);
		_count=sum(_count,1);
	end;
	Last2Avg=_sum/_count;
	drop _:;
run;

I named all the variables I didn't want to keep starting with an underscore (_) and drop them all with that last drop statment.

Re: Average of a partial array with no common prefix

s_lassen — Wed, 29 Mar 2023 12:45:58 GMT

If you are so lucky that there are only numeric variables at the end of your dataset vector, you can use "--" to get the variables in order of definition, e.g.

data want;
  set have;
  array M (*) _numeric_;
  array L(*) m201234--n345;
  Totavg=mean(of M(*));
  avg2=mean(of L(*));
run;

(you should not calculate the mean more than once, the code you showed would just assign the same value to the same variable over and over again)

If your numeric variables are intermixed with characters, you can change the order of the variables by reading character variables in one SET statement and numeric values in another:

data want;
  set have(keep=_NUMERIC_);
  set have(keep=_CHARACTER_);
  array M (*) _numeric_;
  array L(*) m201234--n345;
  Totavg=mean(of M(*));
  avg2=mean(of L(*));
run;

Re: Average of a partial array with no common prefix

Tom — Wed, 29 Mar 2023 12:51:27 GMT

You can also make a list based on the position of the variables that only includes the numeric (or character) variables with the syntax:

array L m201234-numeric-n345;

Note: There is no need to type the (*) (or either of its aliases [*] or {*}) in the ARRAY statement in a data step.

Re: Average of a partial array with no common prefix

FreelanceReinh — Wed, 29 Mar 2023 13:02:36 GMT

Hello @DDawaba and welcome to the SAS Support Communities!

You can also use variable lists such as _numeric_ directly in the MEAN function, so you don't need arrays.

Example:

data want;
set sashelp.baseball;
totavg=mean(of _numeric_);
avg2=mean(of CrBB-numeric-logSalary);
run;

I read the variable names CrBB and logSalary (the first and last item in the list of the last six numeric variables in SASHELP.BASEBALL) off PROC CONTENTS output:

proc contents data=sashelp.baseball varnum;
run;

To avoid this manual step and hardcoding of variable names you can tell PROC SQL to create an equivalent variable list for you:

proc sql noprint nowarn outobs=6;
select name into :last6numvars separated by ' '
from dictionary.columns
where libname='SASHELP' & memname='BASEBALL' & type='num'
order by varnum desc;
quit;

Then replace CrBB-numeric-logSalary by a reference to macro variable last6numvars in the assignment statement for avg2 in the DATA step above:

avg2=mean(of &last6numvars);