Info about SAS-datasets in the WORK-library

In SAS Enterprise Guide it is not very easy to see the size and number of observations in datasets in the WORK-library.

The macro below looks in the DICTIONARY.TABLES and gets these info for the WORK-library. Be aware that it will not work for views, because it’s not doing and actual count of the SAS-datasets.

/********************************************************************************
Author        : 
Creation date : 
Description   : Gets info about datasets in the WORK-library.
Example       : %countwork(print);
*********************************************************************************
Input
-----
&print        : If not empty it will do a PROC PRINT of the dataset WORKDS created
                by the macro.
*********************************************************************************
Output
------
WORKDS        : Contains information about the datasets in the WORK-library.
********************************************************************************/
%macro CountWork(print);
          proc sql;
                   create table workds as
                             select    libname
                                                , memname
                                                , typemem
                                                , nobs format=commax10.0
                                                , filesize format=sizekmg.
                                                , nvar
                             from dictionary.tables
                             where libname eq 'WORK'
                             order by nobs
                   ;
          quit;
 
          %if &print. ne %then
          %do;
                   proc print data=workds;
                   run;
          %end;
%mend;

 

Delete orphan SAS Work-directories on Windows

The code below will delete orphan Work-directories made by SAS. It isn’t always possible for SAS do delete a Work-directory when the SAS-session ends. These Work-directories will take up space on the computer.

The solution is heavily inspired by this – with some minor tweaks and ajustments. It works on Windows XP. Other Windows operating system might not work do to the fact that it uses tasklist.exe to retrieve information about the current running tasks. And other Windows operation systems might have other commands that does this and the output might be a bit different – if that is the case, you need to ajust the macro GetTaskList.

SAS has also made a solution that deletes orphans Work-directories. It’s a part of your SAS-installation, if you choose to install it. It uses the Clean Manager that is build into Windows and can be scheduled through the Windows Task Scheduler. Information about this can be found here.

/***************************************************************************************************************
Description   : Deletes work directories that has no corresponding procesID (PID) from the tasklist in windows.
				The solution is taken from this http://www.mwsug.org/proceedings/2006/coders/MWSUG-2006-CC01.pdf
Example       : %CleanWorkWindows;
***************************************************************************************************************
Parameters
----------
***************************************************************************************************************
Output
------
***************************************************************************************************************/

/***************************************************************************************************************
Description   : Gets SAS-tasks and corresponding procesID (PID) from the tasklist in windows.
Example       : %GetTaskList;
***************************************************************************************************************
Parameters
----------
***************************************************************************************************************
Output
------
Tasklist 	  : Contains the SAS-tasks from the tasklist.exe output.
***************************************************************************************************************/
%macro GetTaskList;
	filename Tasklist pipe "c:\windows\system32\tasklist.exe";

	data Tasklist;
		length PID 8 ProcessName $40;
		InFile TaskList firstobs=4;
		input;
		PID = input(substr(_InFile_,29,4),8.);
		ProcessName = upcase(substr(_InFile_,1,25));
		if index(ProcessName,'SAS.EXE');
	run;
%mend;

/***************************************************************************************************************
Description   : Gets SAS Work-dirs and corresponding procesID (PID) from the SAS-Work directories.
Example       : %GetWorkDirs;
***************************************************************************************************************
Parameters
----------
***************************************************************************************************************
Output
------
WorkDirs 	  : Contains the SAS Work-directories.
&WorkDir	  : Contains the path for the Work-directory in this/thec current SAS-session.
&RootWork	  : Contains the path/location of all the Work-directories.
***************************************************************************************************************/
%macro GetWorkDirs;
	%global WorkDir RootWork;
	%let WorkDir = %sysfunc(pathname(Work,L));
	%let RevWorkDir = %sysfunc(reverse(&workdir));
	%let RootWork = %sysfunc(reverse(%substr(%str(&RevWorkDir),%index(%str(&RevWorkDir),\)+1)));
	%let DirCmd = dir /ad %str(%")&RootWork%str(%");
	filename DirList pipe "&DirCmd";

	data WorkDirs;
		length DirName $80;
		infile DirList;
		input;
		DirName = _InFile_;
		if (substr(DirName,1,1) ne ' ') and (scan(DirName,-1,' ') ne '.') and (scan(DirName,-1,' ') ne '..'); 
			DirName = scan(DirName,-1,' ');
		if substr(DirName,1,1) eq '_' THEN
			PID = input(substr(DirName,4),8.);
		ELSE
			PID = input(reverse(substr(reverse(scan(DirName,2,'_')),1,4)),hex4.);
	run;
%mend;

/***************************************************************************************************************
Description   : Matches the PIDs for SAS from the TaskList in Windows with the PIDs on the SAS Work-directories.
Example       : %FindOrphanDirs;
***************************************************************************************************************
Parameters
----------
***************************************************************************************************************
Output
------
OrphanDir	  : Contains the dirs that should be deleted.
&DoOrNotVar	  : If this is 0 (zero) then there are no orphan Work-directories.
***************************************************************************************************************/
%macro FindOrphanDirs;
	proc sort data=WorkDirs;
		by PID;
	run;

	proc sort data=TaskList;
		by PID;
	run;

	data OrphanDirs;
		merge WorkDirs (in=ActiveDir) TaskList (in=RunningJob);
		by PID;
		if ActiveDir and not RunningJob;
	run;

	proc contents data=OrphanDirs out=DoOrNot noprint;
	run;

	%global DoOrNotVar;

	proc sql stimer noprint;
		select distinct nobs
		into :DoOrNotVar
		from DoOrNot;
	quit;
%mend;

/***************************************************************************************************************
Description   : Makes and executes a .bat-file that deletes the SAS Work-directories that has no corresponding
				PID from SAS-process in the TaskList.
Example       : %DeleteOrphanDirs;
***************************************************************************************************************
Parameters
----------
***************************************************************************************************************
Output
------
***************************************************************************************************************/
%macro DeleteOrphanDirs;
	%if &DoOrNotVar %then
	%do;
		data _null_;
			length Directory Drive $ 256;
			set OrphanDirs;
			file "&WorkDir.\cleanwork.bat";
			if _n_ eq 1 then
			do;
				Drive = compress(scan("&RootWork",1,':') || ':');
				put Drive;
				Directory = 'cd ' || scan("&RootWork",2,':') ;
				put Directory;
			end;
			put "rmdir /s /q " DirName;
		run;

		data _null_;
			set OrphanDirs;
			put "INFO: CLEANWORKWINDOWS: Deleting " Dirname;
		run;

		options xsync noxwait;
		data _null_;
			length CmdLine $127;
			CmdLine = '"' || "&WorkDir.\cleanwork.bat" || '"';
			call system(CmdLine);
		run;
	%end;
	%else
	%do;
		%put INFO: CLEANWORKWINDOWS: No orphan WORK-directories. Nothing to delete.;
	%end;
%mend;

%macro CleanWorkWindows;
	%GetTaskList;
	%GetWorkDirs;
	%FindOrphanDirs;
	%DeleteOrphanDirs;

	/* Cleaning up the Work-directory og the current SAS-session. */
	%put INFO: CLEANWORKWINDOWS: Cleaning up Work-directory in current SAS-session.;
	proc datasets lib=work;
		delete DoOrNot OrphanDirs TaskList WorkDirs;
	quit;
%mend;
%CleanWorkWindows;