Calculating the average based on different columns for different rows

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

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

Thanks,

KP

Trusted Advisor
Posts: 1,630

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;

Occasional Contributor
Posts: 14

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

Thanks Paige

its working

Thanks a lot

Ask a Question
Discussion stats
  • 2 replies
  • 172 views
  • 0 likes
  • 2 in conversation