getvartypecount() macro function

In a prior post, I made mention of the isblank() and getattr() macro functions. I was hoping to find a way to return the number of character or numeric variables in a SAS data set using the ATTRN() SAS function. Alas, the ATTRN() function does not support what I desired neither and does DICTIONARY.TABLES (except in SAS 9.2 (TS2MO) per http://codecraftersinc.com/ Dictionary Tables Reference Card where the num_character and num_numeric columns existed), so I wrote my own custom macro function.

The reason I wanted this was to dynamically set the dimension of an ARRAY that can only be a numeric constant, numeric expression or a constant. This concept works because the macro is resolved prior to the data step, this is the key to assigning the array dimension without an error. This macro function will probably tied into a subsequent post utilizing this function but here is a sneak peak at what I am trying to do:

data x ;
  if 0 then set sashelp.class ; /* Prime the PDV */
  array achar[ * ] _character_ ;
  array acount[ %getvartypecount( dsn = sashelp.class, type = C ), 2 ] ;

  /* .... more code goes here */

run ;

Below is the actual source code used to create the macro. Notice that it does use the %isblank() function referenced in the hyperlink of the first paragraph.

/**************************************************************************
*     Program: getvartypecount.sas
*      Author: Tom Bellmer
*     Created: 26Apr2017
* SAS Version: 9.3 (TS2M2)
*     Purpose: returns number of Character or Numeric columns in a data set  
*       Usage: %let x = %getvartypecount( dsn = sashelp.class, type = C ) ;
*       Notes: Great use to declare ARRAY dimensions since they expect
*              a numeric constant, numeric expression or constant.  Macros
*              are resolved prior to data step code so this works.
**************************************************************************/
 
%macro getvartypecount( dsn =, type = C ) ;
  %local dsid retval i type ;
 
  %if %isblank( &dsn. ) %then %do ;
    %put %str(E)RROR: the DSN value is missing ;
    %return ;
  %end ;
 
  %let retval = 0 ;
 
  %let type = %upcase( &type. ) ;
  %if &type. = C or &type. = N %then %do ;
    %let dsid = %sysfunc( open( &dsn. ) ) ;
    %if &dsid. %then %do ;
      %do i = 1 %to %sysfunc( attrn( &dsid., nvars ) ) ;
        %if &type. = %sysfunc( vartype( &dsid., &i. ) ) 
         %then %let retval = %eval( &retval. + 1 ) ;
      %end ;
      %let dsid = %sysfunc( close( &dsid. ) ) ;
    %end ;
    %else %put %str(E)RROR: Invalid data set name &dsn. ;
  %end ;
  %else %put %str(E)RROR: Only valid TYPE= parameters are C or N ;
 
  &retval. 
%mend ;
 
/* EOF: getvartypecount.sas  */

Column Splitter

I created a report that includes a one page table of contents. Having a single column of all 50 states runs off the page, so I wanted to create additional columns to spread the data horizontally. The %columnSplitter() macro was created to support this capability.

In the below code, several SAS Component Language (SCL) functions are used to process the data. The OPEN() function supports reading data sets in random access instead of sequential access mode. The dsid variable is a pointer to the data set and can be used with many other functions to determine data types, length and number of rows. The %isblank macro code can be found here.

An ARRAY is used to assign the number of columns to create. That value must be determined at compile time, hence the need for the DATA _NULL_ step prior to the assignment of the array subscript. The offset variable is the result by dividing the total number of logical rows by the desired number of columns. The key to the program is the use of the FETCHOBS() function to read a specified row number.

Based on the data type of the &varname variable, a getvarc()/getvarn() function is used to obtain the value of that row. After data for all columns is collected and output, the call missing( of colname[*] ) function is used to reset all array elements to missing. Finally, what gets opened must be closed using the CLOSE() function.

data states ;
  do statecode = 1 to 56 ;
    statename = fipnamel( statecode ) ;
    if statename not in ('Invalid Code', 'District of Columbia' ) then output ;
  end ;
run ;

%macro columnSplitter( 
    columns = 3
  , indsn   =
  , outdsn  =
  , varname =  
) ;

  %local length vartype varnum i j k ;

  %if not %sysfunc( exist( &indsn. ) ) %then %do ;
    %put %str(E)RROR: invalid input data set name: &indsn. ;
    %return ;
  %end ;

  %if %isblank( &indsn. ) %then %do ;
    %put %str(E)RROR: invalid output data set name: &outdsn. ;
    %return ;
  %end ;

  %if %isblank( &varname. ) %then %do ;
    %put %str(E)RROR: invalid variable name: &varname. ;
    %return ;
  %end ;

  data _null_ ;
    dsid = open( "&indsn." ) ;
    varnum = varnum( dsid, "&varname." ) ;
    call symputx( 'varnum', varnum ) ;
    vartype = vartype( dsid, varnum ) ;
    call symputx( 'vartype', vartype ) ;
    call symputx('length',cats(ifc(vartype='C','$',''),varlen(dsid,varnum)));
    dsid = close( dsid ) ;
  run ;

  data &outdsn. ( keep = colname: ) ; 
    array colname[ &columns. ] &length. ; 
    dsid = open( "&indsn." ) ;
    totalobs = attrn( dsid, 'nlobs' ) ;
    offset = ceil( totalobs / &columns. ) ;
    do i = 1 to offset ;
      k = 1 ;
      do j = 0 by offset while( k <= &columns. ) ;
        rc = fetchobs( dsid, i + j ) ;
        if ( i + j ) <= totalobs then do ;
          if "&vartype." = "C" then colname[k] = getvarc(dsid, &varnum.) ;
          else colname[ k ] = getvarn( dsid, &varnum. ) ;
        end ;
        k + 1 ;
      end ;
      output ;
      call missing( of colname[ * ] ) ;
    end ;

    dsid = close( dsid ) ;
    stop ;
  run ;
%mend ;

%columnSplitter(columns=3,indsn=states,outdsn=statessplit3,varname=statename)
%columnSplitter(columns=4,indsn=states,outdsn=statessplit4,varname=statename)

Matrix Validation

Requirements were to examine the data searching for adjacent cells to the right or below and identify cases where the value decreased. An "A" is used in the output data set to signify an issue going across. A "D" signifies a downward violation while a "B" means there were issues going both across and down.

In order to read ahead, I created the test2 data set and added an extra blank row since firstobs = 2 to ensure all data from test gets read in. Arrays are used to generically compare the values and write out any offending values.

data 
  test
  test2
   ( rename = 
     ( x1 = y1 
       x2 = y2 
       x3 = y3 
       x4 = y4 
       x5 = y5 
     ) 
   ) ;

  infile datalines eof = onemore ;
  input x1 - x5;
  output ;
 return ;

 onemore:
    call missing( of _all_ ) ;
    output test2 ;
 return ;

  datalines;
1 2 3 4 5
1 1 1 2 1
4 3 3 2 1
5 4 3 1 2
1 0 1 0 2
;
run ;


data results( keep = ac: ) ;
  set test ( firstobs = 1 ) nobs = nobs  ;
  set test2 ( firstobs = 2  ) ;
  
  array aa[ * ] x: ;
  array ab[ * ] y: ;
  array ac[ * ] $1 ac1 - ac5 ;

  do i = 1 to dim( aa )  ;
    if i < dim( aa ) then do ;
      if aa[ i ] > aa[ i + 1 ] then ac[ i ] = "A" ;
    end ;
    if _n_ < nobs then do ;
      if aa[ i ] > ab[ i ] then do ;
        if missing( ac[ i ] ) then ac[ i ] = "D" ;
        else ac[ i ] = "B" ;
      end ;
    end ;
  end ;
run ;

RGB Color Matrix

There is a scene in the movie Vegas Vacation where cousin Eddie says "I'll have some of the yella and don't get cheap on me". If you were to select a yellow, just how yellow is yellow, how blue is blue, etc...?

RGB (red, green, blue) is an additive color scheme that can produce 16.7 million colors. Each of the primary colors is comprised of hexidecimal (hex) values that range from 00 to FF or 256 unique values. If you take those 256 values to the 3rd power, for each of the 3 RGB color, you get 256**3 = 16,777,216. SAS identifies RGB colors with a CX prefix where CX000000 is black (no color), CXFF0000 is red, CX00FF00 is green, CX0000FF is blue and CXFFFFFF is white (all colors).

The idea for this post comes from a paper by Perry Watts, one of the foremost experts on SAS and colors. What I did was to extract and whittle down the 280 predefined SAS colors in the version 8 documentation and changed some of the grouped assignments. The goal was to create a one page matrix of the 161 colors that were retained. The below image reveals the structure of the retained colors in the CNS (color naming system) SAS data set.

The below code uses proc SQL to determine the size of the two dimensional array and to assign column names to the output data set. A DOW-loop is used to load data into the temporary two dimensional array to properly slot the colors into the correct locations. After that, the data from the temporary array is assigned to the columns and written to the output data set.

The compute block in proc report is used to define the background color using the RGB color code. The next image is the final resulting color matrix.

%macro rgbcolormatrix ;
  %local
    columnnames
    columncount
    tablecolumns
    tablerows
    i
    column
  ;
 
  proc sql noprint ;
    select       group
               , count( * )
      into       :columnnames separated by " "
               , :columncount separated by ","
      from       cns
      group by   group ;
  quit ;
 
  %let tablecolumns = &sqlobs. ;
  %let tablerows = %sysfunc( max( &columncount. ) ) ;
 
  data sascolors( keep = obs &columnnames. ) ;
    length Obs 3 ;
    array atemp[ &tablerows., &tablecolumns. ] $8 _temporary_ ;
    array akeep[ &tablecolumns. ] $8 &columnnames. ;
 
    do until( eof ) ;
      col + 1 ;
      do row = 1 by 1 until( last.group ) ;
        set cns end = eof ;
          by group ;
        atemp[ row, col ] = rgb ;
      end ;
    end ;
 
    do row = 1 to &tablerows. ;
      do col = 1 to &tablecolumns. ;
        akeep[ col ] = atemp[ row, col ] ;
      end ;
      obs + 1 ;
      output ;
    end ;
  run ;
 
  ods listing close ;
  ods pdf
    file      = "%sysfunc( pathname( work ) )\rgbcolormatrix.pdf"
    notoc
    author    = "Tom Bellmer"
    title     = "RGB Color Matrix" ;
 
    options nodate nonumber ;
    title ;
 
    proc report
      data = sascolors
      style( header ) = [ background = cx4d7ebf foreground = cx000000 ]
      nowd
    ;
 
      columns obs ( 'RGB Colors' &columnnames. ) ;
      define obs / center ;
 
      %do i = 1 %to &tablecolumns. ;
        %let column = %scan( &columnnames., &i. ) ;
        compute &column. ;
          if not missing( &column. ) then do ;
            if substr( &column., 3, 1 )
              in ( '0', '1', '2', '3', '4', '5', '6' ,'7' )
              and &column. not in ( 'CX00FF00', 'CX00FFFF' ) then
              colorval = cat( 'style={foreground=white background='
                , &column., ' font_size = 8pt}' ) ;
            else
              colorval = cat( 'style={foreground=black background='
                , &column., ' font_size = 8pt}' ) ;
 
            call define( _col_, 'style', colorval ) ;
          end ;
        endcomp ;
      %end ;
 
    run;
  ods pdf close;
  ods listing;
%mend ;