## Calculating the average based on different columns for different rows

Occasional Contributor
Posts: 14

# Calculating the average based on different columns for different rows

Hi,

Anyone one can help me how can I calculated the average based on different columns for different rows:

e.g:

I want to calculate the field "Average" based on defined column name in "SAvg" to "EAvg":

in first row I want to calculate the average for row M5 to M10 (i.e. 85,589), in second row M5 to M9 (i.e.154,669), and so forth.

 Source M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 SAvg EAvg Average Coll1 £     12,547 £   86,417 £     80,289 £   102,858 £     96,533 £     91,042 £     82,901 £   100,368 £     74,033 £     68,658 M5 M10 Coll2 £   109,891 £   65,897 £     67,685 £   456,987 £   456,987 £     65,897 £   102,985 £     65,897 £     81,577 £     65,897 M5 M9 Coll3 £   138,245 £   93,213 £   104,059 £   456,987 £   456,987 £   456,987 £   101,874 £     93,213 £     65,897 £     65,897 M4 M8 Coll4 £   102,852 £   99,359 £   101,605 £     98,892 £     93,213 £   456,987 £   456,987 £     65,897 £     99,617 £     83,941 M5 M10 Coll5 £     65,897 £   81,481 £     81,481 £   108,522 £   456,987 £     95,261 £     77,686 £   100,531 £     95,412 £   103,649 M4 M7 Coll6 £     65,897 £   86,797 £     82,744 £   138,464 £   456,987 £   107,854 £     97,684 £   116,621 £   456,987 £   456,987 M2 M6

Thanks,

KP

Posts: 3,001

## Re: Calculating the average based on different columns for different rows

I think you could do this via data step ARRAY statements.

UNtested code

data want;

set have;

array m m1-m10;

start=substr(savg,2)+0;

endd=substr(eavg,2)+0;

sum=0;

do i=start to endd;

sum=sum+m(i);

end;

avg=sum/(endd-start+1);

drop i sum start endd;

run;

--
Paige Miller
Occasional Contributor
Posts: 14