Calculating the average based on different columns for different rows

[Kpatel306]

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

[PaigeMiller]

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;

[Kpatel306]

Thanks Paige

its working

Thanks a lot