CODEWITHSUNDEEP
sas
Michael
Michael

Reputation: 13

SAS: How to delete query output files (.sasbdat7 & .log) on program exit or by macro?

I've been instructed to manually delete output files before running a new query. Seems like this could be done as a macro, or a system setting I haven't discovered yet. Do you know how to do this?

Upvotes: 0

Views: 1312

Answers (1)

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

This is a common issue that arises when semi-permanent libraries are being used. It is typical for WORK directory space to be allocated with less space than semi-permanent library directory space. In these cases, semi-permanent directory space is much larger and can be more readily used for big temporary tables. It is also easy to leave old files behind, but there are good practices that you can follow to reduce this.

When you are running a large SAS program that has many different file dependencies, consider having three different spaces:

  1. Temporary Storage - Temporary files that do not need to persist beyond the current session. In SAS, this is the WORK directory.
  2. Semi-permanent Storage - Temporary files that are very large or need to persist beyond the current session, but can easily be recreated without any downtime if an angry sysadmin comes by and says they need space ASAP
  3. Permanent Storage - Files that are critical to a production process and would take a significant effort to recreate or create major downtime

Dealing with Data

When creating SAS programs that store temporary tables in semi-permanent space, create a file naming system that easily identifies temporary tables by prefixes. For example, any tables that you do not want saved permanently you can prefix with an underscore. At the end of each program, you can remove those temporary tables with proc datasets:

proc datasets lib=templib nolist;
    delete _:;
quit;

Your prefix can be as simple or complex as you would like. If you do not have this type of system in place, you will need to manually specify each dataset in proc datasets:

proc datasets lib=templib nolist;
    delete dataset1
           dataset2
           dataset3 
    ;
quit;

Dealing with Logs

Logs in SAS are always temporary unless you tell them to be written somewhere. If they always are written somewhere due to your setup, it would be a good practice to have a temporary archive of them saved by timestamp. Sometimes you don't find a bug until 4 weeks later. Your sysadmin can create a script to zip or delete old files after a certain amount of time.

If this is not possible, you have two ways to deal with it directly in SAS:

1. Using the x command

x needs to be enabled and lets you run OS commands right through SAS. Let's say you're running on Linux. You can delete all files ending in .log with this line:

x 'rm /my/directory/*.log';

If you don't have x commands enabled, things get a little more tricky. You can do this directly through SAS in a data step.

2. Using a SAS program

SAS has a suite of file/folder functions that let you do things like copy, paste, get file attributes, etc. The below program will delete all files ending in .log within a folder.

/* Put your filename here without ending slash.
   If in Windows, change to \ 
*/
%let directory = /my/directory;
filename mydir "&directory";

data _null_;
    length filename $1000.;

    /* Open the directory and assign a Directory ID */
    did = dopen("mydir");

    /* Read every file name in the directory */
    do i = 1 to dnum(did);

        filename = dread(did, i);

        /* If the the filename ends in .log, then delete it */
        if(upcase(scan(filename, -1, '.'))) = 'LOG' then do;

            /* If in Windows, change to \ */
            rc = filename('logfile', cats("&directory./", filename));
            rc = fdelete('logfile');
        end;
    end;

    /* Close the directory */
    rc = dclose(did);
run;

Enclosing this in a macro and saving it to a sasautos space will let you call it elsewhere (e.g. %cleanLogs(directory=/my/directory);).

Upvotes: 2

Related Questions