SyntaxHighlighter

Tuesday, October 30, 2012

Bowling for averages

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

Thursday, October 25, 2012

Make it %local or go loco

Just spent an hour debugging a SAS macro called inside of an outer macro loop and both where using the same macro variable. You should always define macro variables as local within a macro definition. Failure to do so can result in percieved erratic behavior when the outer macro variable value is inadvertantly changed by the inner macro.

Macro parameters are always local by default, but you need to use the %local statement to protect your code. Lesson learned

%macro mymacro( myparam ) ;
  %local myvar ;
%mend ;

Reference: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000206835.htm

Thursday, October 11, 2012

Controlling SAS/AF Frame Columns

I needed a SAS/AF frame to edit a Base SAS data set. The SAS data set contained six columns and I only wanted to expose the first three to the users. I also noticed on my first attempt that user edits were automatically being converted to uppercase even though there were no settings assignend to the data set's format or informat.

The solution to display the columns was to use an SCL list and assign the list to the SAS data set model's columnorder property. To prevent edits going to uppercase, the columns object uppercase property needed to be set to 'No'.

init:
   mdldata.editmode = 'tableleveledit' ; 
return ;

process:
  dcl list collist ;
  collist = { 'start', 'end', 'label' } ;
  mdldata.columnorder = collist ;

  do i = 1 to 3 ;
    /* allow data to be entered in mixed case */
    /* http://support.sas.com/kb/3/925.html   */
    mdldata.columns(i).uppercase = 'No' ;
  end ;
  tblviewer._refresh() ;
return ;