replace max value with different number

[dht115]

Hello, 

I have 12 months observations and I do need to replace maximum value with other value. 

Example:

Mat-1:

I do need to replace dmd12 value as follow. 

I do need to identify maximum value to identify dmd12 has maximum value. 

18

0

27

0

17

2

3

10

3

1

0

40008

Sum is  40089

subtract with maximum number: 40089 - 40008 = 81

divide by 11: 81/11 = 7.3636

int: int(7.3636) = 7

Replace 40008 to 7

Mat-2:

I do need to replace dmd8 value as follow. 

I do need to identify maximum value to identify dmd8 has maximum value. 

1

0

5

10

9

0

8

1008

5

0

0

7

Sum is  1053

subtract with maximum number: 1053 - 1008 = 45

divide by 11: 45/11 = 4.0909

int: int(4.0909) = 4

Replace 1008 to 4

data have;
input item $ dmd1-dmd12;
datalines;
mat1 18 0 27 0 17 2 3 10 3 1 0 40008 
mat2 1 0 5 10 9 0 8 1008 5 0 0 7 
;
run;

/*need to replace maximum dmd value with following logic*/
/*replace_value = int((sum(of DemandMon1-DemandMon12)-maximum dmd)/11);*/

[ErikLund_Jensen · Accepted solution]

Hi @dht115 

I wonder if this is a real-life problem, but it is definitely a good case for playing with arrays in SAS. The following code should do the trick:

data have;
	input item$ dmd1-dmd12;
cards;
mat1 18 0 27 0 17 2 3 10 3 1 0 40008
mat2 1 0 5 10 9 0 8 1008 5 0 0 7
;
run;

data want (drop=maxval maxpos);
	set have;
	array d dmd1-dmd12;
	maxval = max(of d{*});
	maxpos = whichn(maxval, of d{*});
	d{maxpos} = int((sum(of d{*}) - maxval) / 11);
run;

[ChanceTGardener]

If you have SAS/IML 15.1 or higher licensed, you could do it this way in PROC IML: 

data have;
input item $ dmd1-dmd12;
datalines;
mat1 18 0 27 0 17 2 3 10 3 1 0 40008 
mat2 1 0 5 10 9 0 8 1008 5 0 0 7 
;

proc iml;
 use have; /* read data set into IML */
  read all var _NUM_ into X[colname=dmd];
  read all var _CHAR_ into item[colname=char];
 close have;
 
 /* locate maximum value in each row and permanently replace it with desired logic */
 X[loc(X=X[,<>])] = int((X[,+] - X[,<>]) / 11); *print X[c=dmd r=item];

 /* create output data set */
 colNames = char || dmd; 
 create want from item X [colname=colNames];  
  append from item X; 
 close;
quit;