I was recently asked to find the value and name of the column that is closest to the average for a series of columns on a row. Random data was created to emulate the first attempt or roll in the ten frames in bowling.
The below code uses the automatic variable _N_ as a do-loop incrementor and for storing data in array elements. Automatic variables are added to the program data vector (PDV) but not output to a data set. Notice the use of the OF array to calculate the average and the first lowest ordinal value of the difference array (adiff). This was the first time I had occasion to use the vname() function used to identify the name of the column holding the value closest to the average.
data bowling ; length name $8 ; *-- make name first column in PDV ; array frame[ 10 ] ; array adiff[ 10 ] _temporary_ ; do name = 'Billy', 'Joe', 'Bob', 'Bubba', 'Junior' ; do _n_ = 1 to 10 ; *-- create random scores from 0 to 10 ; frame[ _n_ ] = floor( ranuni( 1234 ) * 11 ) ; end ; average = mean( of frame[ * ] ) ; do _n_ = 1 to dim( frame ) ; *-- calc absolute difference between scores and average ; adiff[ _n_ ] = abs( average - frame[ _n_ ] ) ; end ; do _n_ = 1 to dim( adiff ) ; *-- find the score closest to the average ; *-- ordinal() returns smallest difference value from array list ; if adiff[ _n_ ] = ordinal( 1, of adiff[ * ] ) then do ; frame_value = frame[ _n_ ] ; frame_name = vname( frame[ _n_ ] ) ; *-- get column name ; leave ; *-- found it so exit the loop ; end ; end ; output ; end ; run ;
Remeber this dude/dudettes, SAS abides...