Pages

SyntaxHighlighter

Wednesday, December 19, 2018

Macro %then semicolon

As a creature of habit, I typically write %IF %THEN/%ELSE statements using %DO blocks. However, is a %DO block needed for a one line statement? This blog post answers that question via a series of test conditions. Please understand that this is a very simple example program to illustrate the problem that was experienced.

Test 1 single semicolons

%macro test();
   data mydsn;
      %if &sysver = 9.4 %then x = 1;
      %else x = 0;
      date = date();
   run;
%mend;
%test();

80 date = date();   run;
                    ____
                    22
 
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.  

That seems odd, but I guess I need to add another semicolon to each of the %IF and %ELSE lines to terminate the macro language statements. Here are the results of that attempt.

Test 2 - using two semicolons

 73         %macro test();
 74            data mydsn;
 75               %if &sysver = 9.4 %then x = 1;;  /* notice use of two semicolons */
 76               %else x = 0;;                    /* notice use of two semicolons */
ERROR: There is no matching %IF statement for the %ELSE.
ERROR: A dummy macro will be compiled.
 77               date = date();
 78            run;
 79         %mend;
 80         %test();

Test 3 - two semicolons on the %ELSE line

%macro test();
   data mydsn;
      %if &sysver = 9.4 %then x = 1;  /* one semicolon here */
      %else x = 0;;                   /* two semicolons here */
      date = date();
   run;
%mend;
%test();

NOTE: The data set WORK.MYDSN has 1 observations and 2 variables.

While the use of one semicolon on the first line and two on the second macro language line works, I find it confusing. In the name of ease of reading and maintaining, I still prefer the use of %DO blocks.

Test 4 - %DO blocks

%macro test();
   data mydsn;
      %if &sysver = 9.4 %then %do;
       x = 1;
      %end;
      %else %do;
       x = 0;
      %end;
      date = date();
   run;
%mend;
%test();

NOTE: The data set WORK.MYDSN has 1 observations and 2 variables.

Saturday, September 22, 2018

Enterprise Guide Keyboard Macros

Enterprise Guide (EG) supports keyboard macro abbreviations to write out snippets of code directly into a program editor. This makes it easy to supply preset code in a consistent manner without having to remember the syntax.

From the EG menu, select Program | Add Abbreviation Macro… or use Ctrl+Shift+A. Use the below dialog to enter your own abbreviation (e.g. _sql) and associated code in the Text to insert: section.

You can delete, edit, rename or export existing abbreviations or import existing keyboard macro functions (.KMF extension) by selecting Program | Editor Macros | Macros from the menu.

Below is the resulting output of the _hash keyboard macro abbreviation.

data outdsn;
   if 0 then set hashdsn; /* Prime the PDV */

   dcl hash h(dataset: "hashdsn");
   h.definekey('keyvar');
   h.definedata('keyvar1', 'datavar1');
   h.definedone();

   do until(eof);
      set otherdsn end = eof;
      if h.find() ne 0 then call missing(keyvar1, datavar1);
      output;
   end;
   stop;
run;

Friday, September 14, 2018

ColumnLength macro function

There are times when you need to dynamically obtain the data type and length of a SAS data set column. This occurred for me when I was writing a generic post process following a proc means with a conditional class statement. I need to know the data type and length of the class variable so that it can be assigned in the ATTRIB statement.

The below code utilizes the %isblank macro function that can be found here. The results of the function will be a dollar sign ($) if it is a character data type followed by the length of the data set column.

The table reveals the various results of calling this macro:

Call Result Message
%columnlength(dsn=class, column = age); 8
%columnlength(dsn=class, column = name); $8
%columnlength(dsn=classx, column = name); ERROR: The data set classx does not exist.
%columnlength(dsn=class); ERROR: You must pass in a value for column.
%columnlength(dsn=class, column = namex); ERROR: Could not find column: namex

Here is an example of how I used this. I have a macro that includes a proc means/summary with a conditional class statement. After the process is finished I want to control the formatting, order and even add a new column. So this is my code snippet

data &dsn;
   attrib
       yyyymm length = $6
   %if not %isblank(&classvar( %then %do;
      &classvar length = %columnlength( dsn = &dsn, column = &classvar)
   %end;
   ...
%macro columnlength( 
     dsn    = 
   , column = 
);

   %local
      dsid
      position
      type
      retval
   ;

   %if not %sysfunc(exist(&dsn)) %then %do;
      %put %str(E)RROR: The data set &dsn does not exist.;
      %return;
   %end;

   %if %isblank(&column) %then %do;
      %put %str(E)RROR: You must pass in a value for column.;
      %return;
   %end;

   %let dsid = %sysfunc(open(&dsn.));
   %if &dsid %then %do;
      %let position = %sysfunc(varnum(&dsid, &column));
      %if &position = 0 %then %put %str(E)RROR: Could not find column: &column.;
      %else %do;
         %if %sysfunc(vartype(&dsid, &position)) = C %then %let type = $;
         %let retval = &type%sysfunc(varlen(&dsid, &position));
      %end;
      %let dsid = %sysfunc(close(&dsid));
   %end;

   &retval
%mend;

Saturday, September 1, 2018

Stacking datasets

A frequent task is to stack SAS datasets segregated by a time period such as a year/month over time to analyze trends. An easy way to do that is to use the colon modifier as a wildcard at the end of a known pattern. That technique is used to read in data sets starting with x_ or in the second example by the more specific x_2017 and x_2018 patterns.

Sometimes you may have this type of yyyymm pattern, but only want to read in for a set period of time such as 201711 to 201802. The third example uses a macro to handle that scenario. Of course this macro can be augmented to support a where clause or to drop/keep a list of variables.


proc datasets lib = work nolist kill;
quit;

data x_201711 x_201712 x_201801 x_201802;
   call streaminit(1);
   do i = 1 to 4;
      state = fipnamel(ceil(rand('uniform')*50));
           if i = 1 then output x_201711;
      else if i = 2 then output x_201712;
      else if i = 3 then output x_201801;
      else if i = 4 then output x_201802;
   end;
run;

data stack1;
   set x_:;
run;
 
data stack2;
   set x_2017: x_2018: ;
run; 
 

%macro stackds(
     dsnin=
   , dsnout=
   , startyyyymm=
   , endyyyymm=
   , where_clause=
   , drop=
);

   %let start      = %sysfunc(inputn(&startyyyymm.01, anydtdte9.));
   %let end        = %sysfunc(inputn(&endyyyymm.01, anydtdte9.));
   %let diff       = %sysfunc(intck(month, &start, &end));
   
   data &dsnout;
      set 
      %do i = 0 %to &diff;
         %let yyyymm = %sysfunc(putn(%sysfunc(intnx(month,&start,&i,b)),yymmn6.));
         &dsnin&yyyymm
      %end;
      ;
      %if &where_clause ne %then %do;
         where &where_clause;
      %end;
      %if &drop ne %then %do;
        drop &drop;
      %end;      
  run;
 %mend;
 %stackds(
     dsnin = x_
   , dsnout = stack3
   , startyyyymm = 201711
   , endyyyymm = 201802
   , where_clause = state = 'Utah'
   , drop = i)

Sunday, August 26, 2018

SQL - Joining tables witout a common key

It is always best to allow a remote database to handle as much work as possible and return as little data as possible over the network. SQL is a very universal language used by most, if not all database systems.

Recently I had occasion to extract data from SQL Server and found it most efficient to utilize its system tables to do just that. In my case, I wanted both the total record count as well as the number of character and numeric data types. While this information is available using SAS dictionary tables from an ODBC libname statement, it was taking up to four minutes to return the data.

The code below creates two derived tables that contain the desired information. The problem was that there is not a common key to perform the required join. While a cross-join is possible, those joins in PROC SQL write a WARNING to the SAS log which is not allowed in my parselog macro.

The solution was to join the derived table using the ON 1 = 1 syntax. This worked and returned the correct answer in under one second which is much faster than the four minutes using a libname.

proc sql;
   connect to odbc(dsn=mydsn authdomain="myauth");
      create table results as
         select        totalrows
                     , char_cnt
                     , num_cnt
         from connection to odbc(
            select     cnt.totalrows
                     , col.char_cnt
                     , col.num_cnt
            from
            ( select   sum(p.rows) as totalrows                   
              from     sys.tables t
              join     sys.partitions p
                on         t.object_id = p.object_id
                       and p.index_id IN (0, 1)
              where    t.name = 'balancechanges' 
            ) as cnt
            join
            ( select   
                       sum(iif(ty.name in('nvarchar', 'varchar', 'char'
                                        , 'nchar', 'text'), 1, 0)) as char_cnt
                     , sum(iif(ty.name in('nvarchar', 'varchar', 'char'
                                        , 'nchar', 'text'), 0, 1)) as num_cnt
              from     sys.tables t
              join     sys.columns c
                on     t.object_id = c.object_id
              join     sys.types ty
                on     c.user_type_id = ty.user_type_id
              where    t.name = 'balancechanges'
            ) as col
              on       1 = 1 
       );
   disconnect from odbc;
quit; 

Tuesday, August 14, 2018

%juliandate macro function

Astronomers use it and so too does the Google search daterange operator (e.g. daterange:2436900-2436934). The Julian calendar starts at noon universal time (UT), 1 January 4713 BCE (before current era or BC), which is before recorded history. In the business world, Julian date has represented the year and sequential day of that year, now known as an ordinal date. Ever since 1988, the ISO 8601 defines current Julian date usage as astronomers use it.

SAS uses the Gregorian calendar which starts on 15 October 1582 to take into account the inaccuracies of the Julian calendar. According to this article, the Gregorian calendar was developed as a refinement of the Julian calendar, shortening the average year by 0.0075 days to stop the drift of the calendar with respect to the equinoxes. To deal with the 10 days of accumulated drift, the date was advanced so that 4 October 1582 was followed by 15 October 1582.

Of course, SAS uses January 1, 1960 as its days zero (0) so adding 2436934.5 days creates the Julian date. SAS does not have any function to return the ISO 8601 or Julian date rather all Julian type functions from SAS relate to ordinal dates. And according to this SAS knowledge base link, "there are no plans to change the names or functionality" of the SAS functions, informats or formats related to Julian dates.

So if you ever need to obtain a Julian date, just use the below code. The Julian date for 14 August 2018 is 2458344.5.

%macro juliandate(date = %sysfunc(date()));
   %sysevalf(&date + 2436934.5)
%mend;

Tuesday, July 3, 2018

That IFN function

The IFN function returns a numeric value based whether the expression is true, false or missing. The IFC function is used to return character values in a similar fashion. Syntax for the IFN function is as follows:

IFN(logical-expression, value-returned-when-true, value-returned-when-false <, value-returned-when-missing>)

Seems pretty straight forward like a more compact IF-THEN-ELSE statement right? Let's put it to use in a simple query where the character latedate column is used to determine if something runs on a set date or everyday (ALL) as follows:

data x1;
  length latedate $8;
  do latedate = "10", "ALL";
    output;
  end;
run;

proc sql;
  create table x2 as
    select     latedate
             , mdy(7, ifn(anydigit(latedate), input(latedate, 2.), day(date())), 2018)
                as date format = date9.
    from x1 ;
quit;

While the code works, it generates two NOTEs with the keyword 'Invalid' in the SAS log that my parser finds to be in violation.


 NOTE: Invalid string.
 NOTE: Invalid argument to function INPUT. Missing values may be generated.

I found this comment in Rick Wicklin's The DO Loop blog to understand what happened. "All three arguments to the IFN function are evaluated BEFORE the function is called, and the results of the evaluation are then passed to the function."

The way I got around the Invalid NOTEs in the SAS log was to re-write the SQL query using a CASE statement as follows:

proc sql;
  create table x3 as
    select latedate
         , case 
             when anydigit(latedate) then mdy( 7, input(latedate, 2.), 2018)
             else mdy(7, day(date()), 2018)
           end as date format=date9.
    from   x1 ;
quit;

Another way to get around this issue is to use the single ? modifier on the informat to suppress the invalid data messages as follows:

proc sql;
  create table x2 as
    select     latedate
            , mdy(7, ifn(anydigit(latedate), input(latedate, ?2.), day(date())), 2018)
                as date format = date9.
    from x1 ;
quit;

The image comes from the 1998 cult classic movie "The Big Lebowski" and here is the relevant scene. With SAS there is always an IFN way to get things done. The dude abides...

Friday, June 1, 2018

Code that writes code that calls itself

There is a known issue in SAS 9.4M3 when writing out a SAS data set using the ODS EXCEL statement. The issue is that digits to the right of the decimal point are lost when using the PERCENTw.d format. While the problem has been resolved in SAS 9.4M4, I do not have that release.

The below macro code was written to resolve this specific issue, so modify as needed. Also consider that the techniques used here may come into play for other ancillary tasks.

The solution was to write out a VAR statement for each variable in PROC PRINT and conditionally apply the TAGATTR= style attribute. In this case, I elected to have code that writes code to a TEMP filename and then calls itself. The TEMP filename associates with an external file stored in the WROK library so it goes away when the session goes away. Instead of using PROC SQL with dictionary tables to extract the metadata and then pass thru it a second time, I opted to use SAS component language or SCL functions availabe in Base SAS to do the same in a single pass.

What you see next is an example of the generated output.

proc print data = mydsn noobs ;
  var numericvar ;
  var perct1  / style( data ) = { tagattr = "format:###.00\%" } ;
  var perct2  / style( data ) = { tagattr = "format:###.00\%" } ;
run ;

Below is the actual source code to handle this situation.

%macro printpct( dsn = ) ;
  %if not %sysfunc( exist( &dsn. ) ) %then %do ;
    %put %str(E)RROR: &dsn. is not a valid data set. ;
    %return ;
  %end ;
 
  filename pgm temp ;
  data _null_ ;
    file pgm ;
    put "proc print data = &dsn. noobs ;" ;
    dsid = open( "&dsn." ) ;
    if dsid then do i = 1 to attrn( dsid, 'nvars' )  ;
      name = varname( dsid, i ) ;
      put '  var ' name @ ;
      if varfmt( dsid, i ) = 'PERCENT8.2' then 
        put ' / style(data) = {tagattr="format:###.00\%"}' @;
      put " ;" ;
    end ;
    dsid = close( dsid ) ;
    put "run ; " ;
  run ;
  %include pgm ;
  filename pgm clear ;
%mend ;

Thursday, May 3, 2018

DSNS to Excel

A very common task is to copy SAS data sets to Excel. The below macro does just that using the ODS EXCEL statement.

Named parameters are used to accept the input values. The DSNS= parameter accepts one or more one or two level SAS data sets separated by spaces. The %isblank() macro that can be found here is used to look for blank values.

A number of basic integrity checks are performed such as was an output filename supplied and does the path of that file exist? Each of the supplied data sets are examined to ensure they exist before the Excel file is created. The member name (second value in a two level name) is used as the name of the sheet in Excel. This is accomplished by using the %INDEX macro function in conjunction with the SIGN function which is incremented by 1.

/**************************************************************************
*     Program: dsnstoexcel.sas
*      Author: Tom Bellmer
*     Created: 20180503 
* SAS Version: SAS 9.4 (TS1M3)
*     Purpose: Write DSNs to Excel tabs
*       Usage: %dsnstoexcel(dsns=, outfile=, style=) ;
*       Notes: EX: %dsnstoexcel(dsns=sashelp.cars sashelp.class, outfile=/myfolder/x.xlsx ) ;
*
*FL-YYYYMMDD                           Description
*----------- -------------------------------------------------------------
*TB-20180508 Added option for style. Turned on 
*            autofilter, frozen headers and rowheaders
**************************************************************************/
 
%macro dsnstoexcel( 
     dsns    = 
   , outfile = 
   , style   = excel
) ;
 
   %local
      i
      totaldsns 
      jobstarttime 
      slash
      dsn
      position
      name
   ;
 
   %if %isblank( &dsns. ) and %isblank( &outfile. ) %then %do ;
      %put %str(E)RROR: No values passed.  Please use %nrstr( %dsns2excel(dsns=, outfile=) ) syntax. ;
      %return ;
   %end ;
 
   %if %isblank( &outfile. ) %then %do ;
      %put %str(E)RROR: Must pass in values for OUTFILE parameter. ;
      %return ;
   %end ;
 
   %if &sysscp. = WIN %then %do ; 
      %let slash = %str(\) ; 
      options dev = activex ;
   %end ;
   %else %do ; 
      %let slash = %str(/) ; 
      options dev = actximg ;
   %end ;
 
   %if not %sysfunc( fileexist( %substr( &outfile., 1, %sysfunc( find( &outfile, &slash., -999 ) ) ) ) ) %then %do ;
      %put %str(E)RROR: the OUTFILE folder does not exist. ;
      %return ;
   %end ;
 
   %let totaldsns = %sysfunc( countw( &dsns., %str( ) ) ) ;
   %if &totaldsns = 0 %then %do ;
      %put %str(E)RROR: must pass in values for DSNS parameter. ;
      %return ;
   %end ;
 
   %do i = 1 %to &totaldsns. ;
      %let dsn = %scan( &dsns., &i., %str( ) ) ;
      /* verfiy the data sets actually exist */
      %if not %sysfunc( exist( &dsn. ) ) %then %do ;
         %put %str(E)RROR: Data set &dsn. does not exist. ;
         %return ;
      %end ;
   %end ;
 
   %let jobstarttime = %sysfunc( datetime() ) ;
   
   ods _all_ close ; 
   ods results off ;
   ods excel file = "&outfile." style = &style. ;
      %do i = 1 %to &totaldsns. ;
         %let dsn = %scan( &dsns., &i., %str( ) ) ;
         %let position = %eval( %sysfunc( sign( %index( &dsn, . ) ) ) + 1 ) ;
         %let name = %scan(&dsn., &position., . ) ;
 
         ods excel options( 
            autofilter        = 'all'     /* turn on Auto filtering for all columns */
            frozen_headers    = 'on'      /* Freeze first row of data to always see headers */
            frozen_rowheaders = '1'       /* Freeze first column to always see */
            sheet_name        = "&name."  /* name of the sheet */
         ) ;
 
         proc print data = &dsn. noobs ;
         run ;         
      %end ;
   ods excel close ;
   ods results on ;
 
   data _null_ ;
      put "******************************************************************************" ;
      put "     Macro Name: &sysmacroname." ;
      put "     Total DSNs: &totaldsns." ;
      put "Output Filename: &outfile." ;
      put "Completion Time: %left( %sysfunc( datetime(), datetime22. ) )" ;
      put "   Elapsed Time: %left( %sysfunc( putn( %sysevalf( %sysfunc( datetime() ) - &jobstarttime. ), mmss12.3 ) ) )" ;
      put "******************************************************************************" ;
   run ;
%mend ;
 
/*EOF: dsnstoexcel.sas */

Saturday, April 21, 2018

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
 

Saturday, April 7, 2018

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