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 ;

 

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

Here is an update to the original technique that is much more solid. I am presently using SAS 9.4M3 so the highest level method is sas004 but there is a sas005 out there now. As it turns out the _pwencode macro variable is not very reliable at all. So instead I write to a temp file then read the value in and write it out as follows:

%macro setpassword(password =);
   %local pwfile;
 
   %if %isblank(&password.) %then %do;
      %put %str(E)RROR: Must pass a non empty password value.;
      %return;
   %end;
 
   /* drowssap is pasword backwards and dwp is pwd (password) in reverse */
   %let pwfile = /home/&sysuserid/drowssap.dwp;
   /* delete old version of the file if it exists */
   %if %sysfunc(fileexist("&pwfile.")) %then %do;
      systask command "rm &pwfile." wait;
   %end;
 
   filename junk temp;
   proc pwencode 
      in     = "&password." 
      out    = junk   /* outputs to &_pwencode. */
      method = sas004 /* 256-bit fixed key plus a 64 bit random salt value */
   ;
   run;
    
   filename pwd "&pwfile" new;
   data _null_;
     infile junk;
     file pwd;
     input;
     put "/*Password created: %sysfunc(strip(%sysfunc(datetime(),datetime20.)))*/"
        / '%let password = ' _infile_;
   run;
   filename junk clear;
   filename pwd clear;
 
   /* set permissions to RWX for owner only. */
   systask command "chmod 700 &pwfile."  wait;
%mend;
 
/*EOF: setpassword.sas */


/* Password created: 28JUL2018:10:48:40 */
%let password = {SAS004}658F67225DA74D10884E066A29D4E32B31505B939D3B4CFB

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