Sunday, July 9, 2017

Random Libref macro function

Certain SAS procedures such as PROC DATASETS show examples with fixed library references or librefs such as source and dest as follows:

   proc datasets library = source ;
      copy out = dest ;
   run ;

A libref is 1 to 8 characters long, begins with a letter or underscore and contains only letters, numbers or underscores. If you use the SAS dictionary tables you will see that all libnames are stored in upper case.

What if the popularly named librefs of SORUCE and/or DEST are already in use and are not defined in metadata? Since a libref is simply a pointer to a folder (used by Windows and Linux), why not use a random value assigned to a macro variable to avoid any conflicts? The below macro function named %uuidgenlibref() does just that.

The SAS UUIDGEN() function returns a universally unique identifier (UUID) also know as a globally unique identifier (GUID) that looks like this: 83a6f563-67ee-2a47-8498-8f8dc706d0db. As you can see, the first character starts with an 8 which would violate the libref requirement to start with a letter. Here is an example of generating 10 UUIDs from a DO loop:

According to Wikipedia, "In its canonical textual representation, the sixteen octets of a UUID are represented as 32 hexadecimal (base 16) digits, displayed in five groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters (32 alphanumeric characters and four hyphens)."

The macro appends the first seven characters from the UUIDGEN() function using the SUBSTR() function to the 'X' prefix. Here is an example:

   %let sourcelibref = %uuidgenlibref() ;
   %let destlibref = %uuidgenlibref() ;

   libname &sourcelibref. "/lev1/abc" ;
   libname &destlibref. "/lev1/def" ;
   proc datasets library = &sourcelibref. ;
      copy out = &destlibref. ;
   run ;

   libanme &sourcelibref. clear ;
   libname &destlibref. clear ;   

The source consists of a single line of code as you can see below.

*     Program:
*      Author: Tom Bellmer
*     Created: 09Jul2017
*     Purpose: Creates an upper case 8 character libref
*       Usage: %put %uuidgenlibref() ;
%macro uuidgenlibref() ; 
   X%upcase( %substr( %sysfunc( uuidgen() ), 1, 7 ) ) 
%mend ; 
/*EOF: */

Wednesday, April 5, 2017

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 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:
*      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 ;
%mend ;
/* EOF:  */

Wednesday, November 9, 2016

options printerpath =

I was doing a relatively simple ODS PDF proc report using options = landscape; and using ^{thispage} of ^{lastpage}. Here is a snippet of the original code:

ods listing close ;
options orientation = landscape nodate nonumber ;

ods escapechar = '^' ;
ods pdf file = "c:\temp\x.pdf" notoc  ;
title f = arial "My Title" ;
footnote color = black h = 7pt 
  j = l 'Page ^{thispage} of ^{lastpage}'
  j = r "Produced: %sysfunc(date(), mmddyy10.)" ;

proc report data = report nowd 
  style( report header column ) = [ font_size = 8pt fontfamily = arial ]
  style( column ) = [ cellheight = .18in ] ;

  columns ....

I was told that "there are certain situations where SAS changes the default values for the system options LINESIZE and PAGESIZE and then persists these changes from one SAS session to the next. If SAS sets the LINESIZE and PAGESIZE values too small, the following will show up in the log:"

ERROR: Current settings for paper size, orientation and margins
       imply that the printable area has zero size.  Change one
       or more of those settings and try again.
ERROR: Internal error trying to set up "page i of n"
       processing.  Could be caused by out of memory, out of
       disk space, or internal coding error. Check for other
       error messages above indicating probable user error or
       resource constraints; if there are none, or if they make
       no sense, please report this message and provide a test
       program to SAS Institute. Output will continue without
       PAGEOF support.

Martin from SAS technical support suggested I try this usage note: or in the alternative SAS Research and Development suggested I add the following line to my code which worked like a charm.

options printerpath=PDF; 

Friday, July 15, 2016

ValidLibref Macro

Let's say you have a fairly long SAS program and near the top you assign a libref but keyed in the folder incorrectly.

It becomes frustrating to wait for the program to finish only to find out you have a bad library reference or libref. As a result, I wrote the short validlibref() macro that tests for a valid libref ( expecting a zero value ). If the libref is not valid then an ERROR message is written to the SAS log and the %abort cancel statement will stop the program from continuing but will not shut down SAS in interactive mode.

%macro validlibref( libref ) ;
  %if %sysfunc( libref( &libref. ) ) ne 0 %then %do ;
    %put %str(E)RROR: Invalid libref: &libref., terminating program. ;
    %abort cancel ;
  %end ;
%mend ;

%validlibref( sashlp )  /* used the invalid libref of SASHLP vs SASHELP */

/* these statements will not execute if there is not a valid libref */
data x ;
  x = 1 ;
run ;

46   %macro validlibref( libref ) ;
47     %if %sysfunc( libref( &libref. ) ) ne 0 %then %do ;
48       %put %str(E)RROR: Invalid libref: &libref., terminating program. ;
49       %abort cancel ;
50     %end ;
51   %mend ;
53   %validlibref( sashlp )
ERROR: Invalid libref: sashlp, terminating program.
ERROR: Execution canceled by an %ABORT CANCEL statement.
NOTE: The SAS System stopped processing due to receiving a CANCEL request.

Wednesday, June 8, 2016

Rules and Frame Style Attributes Visualized

Style attributes can be used to customize the appearance of SAS Output Delivery System (ODS) output. Styles can be assigned using proc template or style= options in procedures such as PROC REPORT displayed below.

Rules= control the internal borders while frame= control external borders. I have always found it difficult to visualize the combinations of rules= and frame= style attributes. As a result, I created the below code to display all of the combinations in a concise five page PDF file.

ods listing close ;
ods pdf file = "c:\temp\rulesandframes.pdf" startpage = no  ;
options nodate nonumber ;
ods escapechar = "~" noptitle ;

%macro rulesandframes ;
    j ;

  %let countframes = %sysfunc( countw( &frames. ) ) ;
  %let rules = ALL COLS GROUPS NONE ROWS ;
  %let countrules  = %sysfunc( countw( &rules. ) ) ;

  %do i = 1 %to &countrules. ;
    %let rule = %scan( &rules., &i. ) ;
    title "RULES = &rule. ( internal borders )" ;

    %do j = 1 %to &countframes. ; 
      %let frame = %scan( &frames., &j. ) ;
      ods text = 
       "~{style[just=center]style(report) = [rules = &rule. frame = &frame.]}" ;
      proc report 
        data = sashelp.class( obs = 2 ) 
        style( report ) = [ rules = &rule. frame = &frame. ] ;
      run ;
    %end ;

    ods pdf startpage = now ;
  %end ;

%mend ;

ods pdf close ;
ods listing ;

Saturday, May 28, 2016

Compress Non-Printable Special Characters

Have you ever been bitten by flies that you feel but can't see? Those flies are often referred to as no-see-ums.

You can also get stung by non-printable and special characters (NPSC) in computer programming. Non-printable characters are those that have an ASCII decimal value of between 0 and 31 as well as 127 (DEL character) in the standard ASCII character set. Special characters are the high level ASCII decimal codes from 128 thru 255 inclusive.

Those pesky invisible characters can wreak havoc in the results of a process (see this paper for additional information). What I found out was that the COMPRESS() function with the KW (keep writable) modifier did not work in my case. I had the ASCII decimal 160 character (AO in hex) as the first character in a variable.

As a result, I wrote a function using PROC FCMP to eradicate those annoying characters and replace them with nulls. Below is the source code and a test program to remove the no-see-ums.

Be sure to assign a LENGTH to the returned variable as the function will otherwise return a string that is 32,767 characters in length. The default return length for proc fcmp is 33 characters so I set it to the maximum width to avoid truncation.

proc fcmp outlib = work.funcs.utilities ; 
  function compress_npsc( var $ ) $32767 ; 
    length npschars $256 ; 
    do i = 0 to 31, 127 to 255 ; 
      npschars = cats( npschars, byte( i ) ) ; 
    end ; 
    return( compress( var, npschars ) ) ; 
  endsub ; 
run ; 

options cmplib = ( work.funcs ) ; 

data test ; 
  length sc cc $4 ;
  sc = cat( byte( 160 ), 'ABC' ) ; 
  cc = compress_npsc( sc ) ; 
  rank_sc = rank( sc ) ;
  rank_cc = rank( cc ) ;
  hex_sc = put( sc, hex8. ) ;
  hex_cc = put( cc, hex8. ) ;
run ;

Friday, April 22, 2016

SQL Pass-Through Derived Table Join to SAS dataset

I have used the SAS/Access Interface to OLE DB for years. In the past I would capture data from a relational database management system (RDBMS) into a SAS data set and then join that remote data to a local SAS data set using another proc or data step.

Having to read the data more than once seemed inefficient, so I attempted to reference the pass-through data as a derived table (aka in-line view) and join that data directly to a SAS data set. Sure enough, as the below code using SQL Server Express shows this can be done.

In the simplified example below, a SAS data set named SASCAT is created containing the groupname and an abbreviated name (abbrname). After that, an OLEDB connection is made to the freely downloadable SQL Server Express database.

The derived table consists of the SQL statements contained between the parenthesis starting with the FROM CONNNECTION TO OLEDB keywords. That derived table is referenced as SQL which is then joined to the SAS data set sascat which is referenced as SAS and combined as an inner join using the ON clause.

%macro dbconnect( server= ) ;
  /* DSN-less OLEDB connection */
  connect to oledb
   (init="Provider=SQLNCLI11;Integrated Security=SSPI;Data Source=&server.") ;
%mend ;

%macro dbdisconnect() ;
  disconnect from oledb ;
  %if &sysdbrc ne 0 %then %put %str(E)RROR: %superq( sysdbmsg ) ;
%mend ;

data sascat ;
    groupname & $50.
    abbrname  : $4. ;
  datalines ;
  Executive General and Administration  Exec
  Inventory Management  Imgt
  Manufacturing  Mfg
  Quality Assurance  QA
run ;

proc sql ;
  %dbconnect( server = localhost\SQLExpress ) 
    create table results as
      select        sas.abbrname
                  , sql.count

      from          connection to oledb
        select      groupname
                  , count( groupname ) as count
        from        adventureworks2014.humanresources.department 
        group by    groupname
      ) sql

      join          work.sascat sas
        on          sql.groupname = sas.groupname 
quit ;