STREXTRACT custom FCMP function

There are 906 functions supplied by SAS in release 9.2 TS2M3. You can verify this by using the SAS dictionary.functions table or sashelp.vfunc view.

Unfortunately, none of the standalone SAS supplied functions did what I desired when I needed to extract values between some nodes in XML. The good news is that SAS offers PROC FCMP so you can create your own custom functions.

The custom STREXTRACT function accepts as a parameter an input string that can be up to 32,766 characters in length. There is an issue in SAS release 9.2 that has been corrected in 9.3 that does not allow the standard SAS maximum value of 32,767 in proc fcmp when concatonating strings ( http://support.sas.com/kb/41/912.html ).

After the input string, supply the begin and end delimiters. Those delimiters are usually different in XML and HTML where a slash (/) signifies a closing tag.

The fourth and final parameter (occurance) can accept a postive integer value to extract a single node value or you can use a zero (0) to concatonate and return all the node values.

/**************************************************************************
*    Function: strextract
*      Author: Tom Bellmer
*     Created: 17APR2013  
* SAS Release: 9.2 TS2M3
*     Purpose: extracts strings between delimiters
*       Usage: x = strextract( str, '', '', 0 ) ;
**************************************************************************/

proc fcmp outlib = work.mysub.temp ;
  function strextract( string $, begindelimiter $, enddelimiter $, occurance ) $ ;
    length retval tempstr $32766 ;

    if occurance = 0 then upperlimit = count( string, begindelimiter ) ;
    else upperlimit = occurance ;

    startposition = 1 ;
    do i = 1 to upperlimit ;
      findstart     = find( string, begindelimiter, startposition ) ;
      findend       = find( string, enddelimiter,   findstart + 1 ) ;
      startposition = findend ;
      tempstr       = substr( string, findstart + lengthn( begindelimiter )
                        , findend - findstart - lengthn( begindelimiter ) ) ;
      if occurance = 0 then retval = catx( " ", retval, tempstr ) ;
    end ;
    if occurance ne 0 then retval = tempstr ;

    return( retval ) ;
  endsub ;
quit  ;

options cmplib = work.mysub ;
data demo( drop = xml str ) ;
  xml  = "< xml>< node>One< /node>< node>Two< /node>< node>Three< /node>< /xml>" ;
  str  = "|One|Two|Three|" ;
  xml0 = strextract( xml, '< node>', '< /node>', 0 ) ;
  str0 = strextract( str, '|', '|', 0 ) ;

  array axml[ 3 ] $8 ;
  array astr[ 3 ] $8 ;
  do _n_ = 1 to 3 ;
    axml[ _n_ ] = strextract( xml, '< node>', '< /node>', _n_ ) ;
    astr[ _n_ ] = strextract( str, '|', '|', _n_ ) ;
  end ; 
run ;

Truncated Record

You can do some pretty nice things using ODS PDF LAYOUT and its ODS TEXT= commands. In my case I was writing some long strings used as paragraphs that did automatic word wrapping when rendered. This all worked great in interactive mode, however, the same could not be said when I took the exact same code and tried to run it as a scheduled task.

The error I would get is TRUNCATED RECORD when running the code in batch. The solution is to have the Task Scheduler call a SAS program that does a %INCLUDE of the original code and contains a LRECL (logical record length) that is greater than the default of 256 bytes. So in the end my batch SAS call looks like this:

%include "e:\mypath\originalcode.sas" / source2 lrecl = 32767 ;

Problem solved! The source2 option writes the original included code to the SAS log and I used the maximum record length allowed of 32767.

Multi Server Join using SAS/Access ODBC

It is always best to do most of the processing on a server and only return the filtered results back to the requesting client. In this case I need two columns (state and county) otherwise I might have simply created a macro variable if only a single column were required.

The LIBNAME statement is used to bulk copy (BCP) 8 rows from DEV into the global (##) temp table. Those 8 rows are then joined to an 8 million row table and the entire process takes less than one second.

/* store data into a global (##) temporary table on SQL Server */
libname sqltemp 
  odbc 
  noprompt   = "Driver={SQL Server};Server=devserver;Trusted_Connection=Yes;" 
  bcp        = yes   
  connection = global ;

proc sql ;
  connect to odbc as prod 
    ( "Driver={SQL Server};Server=prodserver;Trusted_Connection=Yes;" ) ;

    /* populate data from prod into a dev SQL Server global temporary table */
    create table sqltemp.'##counties'n as
      select           *
        from           connection to prod
        ( select       *
            from       db.schema.table
            where          statecode  = &state. 
                       and countycode = &county. ) ;
  disconnect from prod ;

  connect to odbc as dev 
    ( "Driver={SQL Server};Server=devserver;Trusted_Connection=Yes;"  ) ;

    create table work.results as
      select            *
        from   connection to dev
        ( select         a.*
            from         db.schema.table a
            inner join   ##counties b        /* SQL Server temp table */
              on             a.state  = b.state
                         and a.county = b.county ) ;
  disconnect from dev ;
quit ;

libname sqltemp clear ;

xtab using proc report and tabulate

Here is an example to transpose data to a cross tab report using proc report and proc tabulate.

data input ;
  do _n_ = 26 to 31 ;
    do year = 2009 to 2012 ;
      date = put( mdy( 12, _n_, 2012 ), yymmdd10. ) ;
      value = round( ranuni( 1 ) * 1000, .01 ) ;
      output ;
    end ;
  end ;
run ;

The below image contains the results of both procedures. The top result is from proc report followed by proc tabulate.

ods listing close ;
ods pdf file = "c:\temp\xtab.pdf" startpage = no style = sasweb ;
 
  proc report data = input nowd ;
    column date year, value ('Daily Total' value = value2) ;
 
    define date   / group  '' ;
    define year   / across  '' ;
    define value  / analysis sum '' format = dollar10.2 ;
    define value2 / analysis sum '' f = dollar10.2 ;
 
    rbreak after / dol skip summarize ;
    compute after ;
      date = 'Total' ;
    endcomp ;
  run ;
 
  proc tabulate data = input ;
    class date year ;
    var   value ;
    table    
          date = '' all = 'Total'
      , ( year = '' all = 'Daily Total' ) * value = '' 
            * sum = '' * f = dollar10.2 ;
  run ;
 
ods pdf close ;
ods listing ;

ODS TEXT = symbol

This blog post covers how to add a symbol from a font, in this case the up arrow from the Wingdings font.  The symbol I wanted to use was not a unicode character that I could find so I contacted SAS technical support.

If you use the Windows Character Map ( Start | All Programs | Accessories | System Tools | Character Map ), you will see the value as 0xE9.  This can be converted to a numeric value using a hex input format as follows:

value = input( 'E9', hex2. ) ;

An alternative way is to use Microsoft Word, click on the Insert toolbar then Symbols ribbon to open the Symbol dialog box (see below).

Now to render the 233 Wingdings symbol in ODS TEXT= do the following:

ODS TEXT = "^{style[font_face=Wingdings]%sysfunc(byte(233))}" ;

1 Color in 16.7 million

SAS / Graph and other applications use hexidecimal (base 16 or hex) values for colors. A single hex digit (1/2 an octet) can contain 16 values using 0 thru 9 then A (10) thru F (15). A byte can handle 256 colors (0 to 255 decimal or 00 to FF hex). A red, green, blue (RGB) primary color triplet (24 bit) can handle 16,777,216 colors as can be seen here:

data _null_ ;
  color1 = 16 ** 2 ;
  color3 = color1 ** 3 ;
  put ( color: ) ( @5  = comma10. / ) ;
run ;

    color1=256
    color3=16,777,216

If you need to obtain a specific computer color, you can do that using Microsoft Paint by following these steps:

1. Press Alt-PrintScreen to capture the screen
2. Start MS Paint and paste (control-V) the image
3. Click on the Color picker (eye dropper symbol)

4. Click on the desired color
5. Click on Edit Colors to see the decimal values

In this exmaple the returned decimal RGB values are 106, 89, 59. The following SAS macro function will convert the decimal RGB values to a SAS color hex (CX) value.

%macro rgbtohex( r, g, b ) ;
 CX%sysfunc(putn(&r.,hex2.))%sysfunc(putn(&g.,hex2.))%sysfunc(putn(&b.,hex2.))
%mend ;

%put %rgbtohex( 106, 89, 59 ) ;

CX6A593B

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