Macro Variable Tips

Below are a few relatively unknown features related to SAS macro variables.

Most of us know about PROC SQL and the INTO :macro-var option to create macro variables. In this example, I have created a data set containing a numeric column named SSN that is 9 bytes wide. Be sure to add the NOPRINT option to avoid writing to the output window.

The first example has issues because a numeric value over 8 bytes gets converted to scientific notation. Notice the use of the TRIMMED option, new in SAS 9.3, on the &trimmed_range macro variable to strip leading and trailing spaces. Vertical bars were used to illustrate the leading spaces. In the next example, the format = 9. modifier is used to preserve the desired output - no more scientific notation.

The last example uses the unbounded macro variable range which was added in SAS version 9.3. In the past I have seen queries run to find the row limit and then use that value to define the upper boundary. Personally, I have used boundary limits such as :x1 - :x9999 but that is no longer required as you can simply use INTO :x1 - . Both of these techniques will only create the number of macro variables needed. That is if you use either INTO :X1 - :X9999 or INTO :X1 -, only &X1 and &X2 will be created using the below example.

Starting in SAS 9.3 you can use the &=macro-var syntax. According to SAS documentation, if you place an equal sign between the ampersand and the macro variable name of a direct variable reference, the macro variable's name displays in the log along with the macro variable's name. You can see that in the last part of the output below.

data ssns ;
  input ssn ;
  datalines ;
123456789
987654321
;
run ;
 
proc sql noprint ;
   select  count(*)
         , count(*)
   into    :untrimmed_range    
         , :trimmed_range trimmed   
   from    ssns ;
 
   select  ssn 
   into    :a1 - :a&trimmed_range  /* &trimmed_range defines the upper limit */
   from    ssns ;
   
   select  ssn format = 9. /* FORMAT= prevents scientific notation */
   into    :f1 - :f9999
   from    ssns ;
   
   select  ssn format = 9.
   into    :u1 -       /* unbounded macro-variable range */
   from    ssns ;
quit ;  
 
 
%put untrimmed: |&untrimmed_range| ;
untrimmed: |       2|
%put trimmed:   |&trimmed_range| ;
trimmed:   |2|
%put &=a1 ;
A1=1.2346E8
%put &=f1 ;
F1=123456789
%put &=u1 ;
U1=123456789
 

Copy/Delete/Move files - without XCMD

The ability to copy, delete or move an external file is frequently a requirement of an application. These actions are often performed using an X command, FILENAME, CALL SYSTEM, %SYSEXEC or other tasks that try to execute a shell-level command. Because all of the previously mentioned commands are contingent on the setting of XCMD, which is often set to NOXCMD, they are subject to failure.

One way to get around the XCMD limitation is to use low-level SAS functions found in the SAS Component Language or SCL that are also part of Base SAS language. Those functions include FILENAME(), FEXIST() and FDELETE(). The new SAS 9.4 FCOPY() function is also utilized. Using SAS functions instead of operating system specific calls makes the code independent of settings and portable.

All three of the below macro functions will return a value indicating success (1) or failure (0). The ability to evaluate the return value make these macros more flexible. In the case of the %COPYFILE macro, conditions such as the input and output filenames must exist or the function is not performed. Similarly, the %DELETEFILE macro also requires a valid filename exist. Finally, the %MOVEFILE macro leverages both of the prior macros and requires that the %COPYFILE macro complete successfully or else the subsequent %DELETEFILE action will not take place.

Because of the inherit return value, macro functions need to be called from inside an %IF or using a %LET rc = statement. Here are some examples to help illustrate, followed by the full source code:

%if %copyfile(infile=/folder/inf.txt, outfile=/folder/outf.txt) %then %do ; 
%let rc = %copyfile(infile=/folder/inf.txt, outfile=/folder/outf.txt) ;

/**************************************************************************
*     Program: copyfile.sas
*      Author: Tom Bellmer
*     Purpose: macro to copy a single file from source to destination 
**************************************************************************/
%macro copyfile( 
     infile  =
   , outfile = 
) ;

   %local
      rc
      retval
      _bcin
      _bcout
   ;

   %let retval = 0 ;

         %if not %sysfunc( fileexist( &infile. ) ) %then 
            %put %str(E)RROR: Infile = &infile. does not exist. ;
   %else %if %isblank( &outfile. ) %then 
            %put %str(E)RROR: must supply an outfile= value. ;
   %else %do ;

      /* create a fileref of the file using recfm=n or binary format */
      %let rc = %sysfunc( filename( _bcin, &infile., , recfm = n ) ) ;
      %if &rc. = 0 and %sysfunc( fexist( &_bcin. ) ) %then %do ;

         %let rc = %sysfunc( filename( _bcout, &outfile., , recfm = n ) ) ;
         /* copy the file using FCOPY() */
         %if &rc. = 0 %then %let rc = %sysfunc( fcopy( &_bcin., &_bcout. ) ) ;

         %if &rc = 0 %then %do ;
            %put %str(N)OTE: Copied file: &infile. to &outfile. ;
            /* set retval = 1 to a success value from the function */
            %let retval = 1 ;
         %end ;

         %else %put %sysfunc( sysmsg() ) ;
         /* deassign the file references */
         %let rc = %sysfunc( filename( _bcin ) ) ;
         %let rc = %sysfunc( filename( _bcout ) ) ;

      %end ;

   %end ;
  
   /* return the status of the process where 1 = success */
   &retval.
%mend ;

/*EOF: copyfile.sas */

/**************************************************************************
*     Program: deletefile.sas
*      Author: Tom Bellmer
*     Purpose: macro function to delete a single file
**************************************************************************/
%macro deletefile( filename ) ;
   %local 
      retval 
      rc
      fref
   ;

   %let retval = 0 ;

   %if not %sysfunc( fileexist( &filename. ) ) %then 
      %put %str(E)RROR: &filename. does not exist. ;
   %else %do ;
      /* get a fileref */
      %let rc = %sysfunc( filename( fref, &filename. ) ) ;

      %if &rc. = 0 and %sysfunc( fexist( &fref. ) ) %then %do ;
         /* delete the file */
         %if %sysfunc( fdelete( &fref. ) ) = 0 %then %do ;
            %put %str(N)OTE: Deleted file: &filename.. ;
            %let retval = 1 ;
         %end ;
      %end ;

      /* release the filefref */
      %let rc = %sysfunc( filename( fref ) ) ;
   %end ;

   &retval.
%mend ;

/*EOF: deletefile.sas */

/**************************************************************************
*     Program: movefile.sas
*      Author: Tom Bellmer
*     Created: 20180315
**************************************************************************/
%macro movefile( 
     infile  =
   , outfile = 
) ;

   %local 
      retval 
      rc
      fref
   ;

   %let retval = 0 ;

         %if not %sysfunc( fileexist( &infile. ) ) %then 
            %put %str(E)RROR: Infile = &infile. does not exist. ;
   %else %if %isblank( &outfile. ) %then 
            %put %str(E)RROR: must supply an outfile= value. ;
   %else %do ;

      /* copy then delete the input file */
      %if %copyfile( infile = &infile., outfile = &outfile. ) %then %do ;
         /* delete file only if a successful copy */
         %let retval = %deletefile( &infile. ) ;
      %end ;

   %end ;

   &retval.
%mend ;

/*EOF: movefile.sas */