SyntaxHighlighter

Tuesday, December 5, 2017

DOSUBL() - execute macro code in a data step

The DOSUBL() function, new in SAS 9.4, requires us to rethink the way we look at passing values to a SAS macro. Previously I would create a series of macro variables using PROC SQL with the INTO clause such as :macvar1 - :macvar999 or by using CALL SYMPUTX().

If you try to pass data set variables into a macro they are rendered as the column names, not the column values. The way I used to do this required a %DO loop inside a macro. Now I can use the CATS() function to concatenate strings inside the DOSUBL function. As you can see in the below code, I am passing in a data set variable directly into a macro and using the RESOLVE() function to render its value. The return value of DOSUBL is zero if it was successful.

%macro createfile( filename =, text = ) ;
   filename temp "&filename." ;
   data _null_ ;
     file temp ;
     if not missing( "&text." ) then put "&text." ;
   run ;
   filename temp clear ;
%mend ;

%let path = %sysget(HOME) ;
data files ;
   input filename $64. ;
   datalines ;
&path./a.txt
&path./b.txt
;
run ;

/* Old technique */
%macro sql1() ;
  proc sql noprint ;
    select   filename
    into     :file1 - :file999
    from     files ;
  quit ;

  %do I = 1 %to &sqlobs. ;
    %createfile( filename = &&file&i ) ;
  %end ;
%mend ;
%sql1()

/*New technique */
data _null_ ;
  set files ;
  rc = dosubl(cats('%createfile(filename=',resolve(filename),')'));
run ;
 

Saturday, December 2, 2017

Connect to Server macro

In my last post I created an encoded password file. This macro utilizes the password to make a connection to a SAS/Connect server.

After your password is read in from the password file, you need to specify the name of the SAS/Connect server and its listening port. Once complete, you specify a few options, all of which are described in the below code. After that, you invoke the SIGNON command. The rest of the code does some fundamental error handling.

It is always a good idea to encapsulate code into a single reusable module for maintainability. In this case that container is a SAS macro. What follows is the entire source code to connect to a SAS/Connect server.

/**************************************************************************
*     Program: connecttoserver.sas
*      Author: Tom Bellmer                                                        
*     Created: 20171202
* SAS Version: SAS 9.4 (TS1M3)
*          OS: Linux
*     Purpose: Establish SAS/Connect session to 1SG
*       Usage: %connecttoserver()
*        Note: Be sure to add SIGNOFF SASGRID ; when finished.
**************************************************************************/

%macro connecttoserver() ;
   %global sasgrid ;

   /* read in the encoded password */
   %inc "%sysget(HOME)/drowssap.dwp" ;

   /* 7551 is the listening port of the Connect Spawner */
   %let sasgrid = yourhostname 7551 ;

   options 
     comamid             = tcp     /* Communications access method */
     remote              = sasgrid /* Points to &sasgrid, no & required */
     netencryptalgorithm = aes     /* value of remote server */
     noconnectmetaconnection       /* Bypass metadata authentication */
   ;

   signon sasgrid  
     cmacvar       = signonstatus  /* macro var to validate connection */
     connectstatus = no            /* No Status window  */
     noscript                      /* Not using script, this is faster */
     signonwait    = yes           /* wait for connection */
     username      = "&sysuserid."   
     password      = "&password." 
   ;

   %if &signonstatus. = 0 %then %do ;
     %put %str(N)OTE: Signon to SASGRID on host &syshostname. is successful. ;
   %end ;

   %else %if &signonstatus. = 2 %then %do ;
     %put %str(N)OTE: Connected to existing session on host &syshostname.. ;
   %end ;

   %else %do ;
     %put %str(E)RROR: Signon to SASGRID failed, (return code: &signonstatus.) ;
     %abort cancel ;
   %end ;
%mend ;

/*EOF: connecttoserver.sas */

Encoded Password

I was using SAS/Connect to submit jobs across SAS grids. This all worked for me when I hardcoded my password but not for others because they have different passwords.

The solution I came up with was to utilize PROC PWENCODE with the sas003 method. If your organization does not have SAS/SECURE then you will need to change to the default method of sas002. The sas003 method uses a 256-bit key plus 16-bit salt to encode passwords and will also be FIPS 140-2 compliant. FIPS 140-2 is a U.S. government computer security standard used to approve cryptographic modules.

Every UNIX user has a HOME environmental variable which contains the user's specific HOME directory that can be obtained via the SYSGET(HOME) command. Note that HOME must be in uppercase.

In order to obfuscate the password filename, I used reversed "password" to "drowssap" with an extension of "dwp" or "pwd" backwards. Once the file is created on UNIX/Linux, I used SYSTASK to set the permission to 400 or read only by the creator of the file.

Once the file has been created, it is simply a matter of run the following line of code:

   %INCLUDE “%sysget(HOME)/drowssap.dpw” ;

Here is an example of the generated output file followed by the full source code.

   /*Password created: 02DEC2017:15:18:29*/
   %global password ;
   %let password = {SAS003}24F08AC76DBA3044B14EE06846D015D1D250 ;


/**************************************************************************
*     Program: setpassword.sas
*      Author: Tom Bellmer
*     Created: 20171202
* SAS Version: SAS 9.4 (TS1M3)
*          OS: LIN X64
*     Purpose: Create a proc pwencode(d) password and set the permissions 
*              to read only or 400
*       Usage: %setpassword( password = )
*       Notes: After created  %inc "%sysget(HOME)/drowssap.dwp" ;
**************************************************************************/

%macro setpassword( password = ) ;
   %local pwfile ;

   %if %isblank( &password. ) %then %do ;
      %put %str(E)RROR: Must pass a non empty password value. ;
      %return ;
   %end ;

   filename junk temp ;
   proc pwencode 
      in     = "&password." 
      out    = junk /* outputs to &_pwencode. */
      method = sas003 /* 256-bit fixed key plus a random salt value */
   ;
   run ;
   filename junk clear ;

   /* drowssap is pasword backwards and dwp is pwd (password) in reverse */
   %let pwfile = %sysget(HOME)/drowssap.dwp ;

   /* delete old version of the file if it exists */
   %if %sysfunc( fileexist( "&pwfile." ) ) %then %do ;
      systask command "rm &pwfile." wait ;
   %end ;

   filename pwd "&pwfile." new ;
   data _null_ ;
     file pwd ;
       put "/*Password created: %sysfunc(strip(%sysfunc(datetime(),datetime20.)))*/" 
        / '%global password ;'
        / '%let password = ' "&_pwencode. ;" 
      ;
   run ;
   filename pwd clear ;

   /* set permissions to read for user only. */
   systask command "chmod 400 &pwfile."  wait ;
%mend ;

/*EOF: setpassword.sas */

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: uuidgenlibref.sas
*      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: uuidgenlibref.sas */

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 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  */

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: http://support.sas.com/kb/41/196.html 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 ;
52
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.