SAS – Page 5 – Ryslander.com

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;

How to give your SAS-session a name

The DOS batch code below let’s you give your SAS-session a name – instead of just showing “SAS” on the procesline. The code gives you the possibility to differentiate multiple SAS-sessions from each other on the process line.
You just make a .bat-file containing the code below. Then you drag and drop the shortcut for your SAS-session onto the .bat-file and the .bat-file will start by asking you to give the SAS-session a name. When you have entered a name and pressed the Enter-key, it will start a SAS-session showing the name you just entered.

@echo off
cls
set /P SASName=Please enter name of SASSession:
%1 -AWSTITLE "%SASName%"

You will have to drag and drop your SAS-shourtcut on to the .bat-file containing the code above.

The .bat-file will ask you to give the SAS-session a name.

Now you will have open SAS-session on the process line in Windows with the name you have just given them.

Getting filename from SYSIN-option in SAS

The code below makes it possible to extract the filename from the filename being executed through the SYSIN-option in SAS.

%macro GetProgramNameFromBatch(_PgmName=);
        %let PgmName = &_PgmName;
        %let PgmNameRev = %sysfunc(reverse(&PgmName));
        %let SlashPos = %index(&PgmNameRev, /);
        %if &SlashPos eq 0 %then
        %do;
                %let SlashPos = %index(&PgmNameRev, \);
        %end;
        %if &SlashPos ne 0 %then
        %do;
                %let PgmNameRev = %substr(&PgmNameRev, 1, %eval(&SlashPos-1));
                %let PgmName = %sysfunc(reverse(&PgmNameRev));
        %end;
        %else
        %do;
                %let PgmName = %sysfunc(reverse(&PgmNameRev));
        %end;

        &PgmName
%mend;

%let PgmName = %GetProgramNameFromBatch(_PgmName=/sas/Batchjobs/Test.sas);
%put Programname = &PgmName;

%let PgmName = %GetProgramNameFromBatch(_PgmName=c:\sas\Test_sysin.sas);
%put Programname = &PgmName;

%let PgmName = %GetProgramNameFromBatch(_PgmName=Test_sysin.sas);
%put Programname = &PgmName;

Truncate (empty) an existing SAS-dataset

The code below will makes it possible to truncate (empty) an existing SAS-dataset.

data SASDataset;
  if (0=1);
run

Include macros in SAS

This macro will include the external macros in a library on the disk. This will be done, so you are sure that SAS will choose the external macros not the same macros in libraries included in SASAUTOS. The macros first choosen by SAS will be the macros found in Work.Sasmacr library. Including the external macros will put these macros in this library. Example : %IncludeExternalMacros(dir=&RootPath/Macros/External/);

%macro IncludeExternalMacros(dir=);
%local fileref rc did dnum dmem memname filename didc;                                                                                
%let rc=%sysfunc(filename(fileref,&dir));                                                                                             
%let did=%sysfunc(dopen(&fileref));                                                                                                   
%if &did=0 %then %do;                                                                                                                 
%put ERROR: Directory does not exist;                                                                                               
%put ERROR: The macro will terminate;                                                                                             
%return;                                                                                                                            
%end;                                                                                                                                 
%let dnum=%sysfunc(dnum(&did));                                                                                                       
%do dmem=1 %to &dnum;                                                                                                                 
%let memname=%qsysfunc(dread(&did,&dmem));                                                                                          
%if %qupcase(%qscan(&memname,-1,.)) = SAS %then %do;                                                                              
%let filename=%scan(&memname,1,.);                                                                                              
%inc "&dir.&filename..sas";                                                                                                     
%end;                                                                                                                             
%end;                                                                                                                                 
%let didc=%sysfunc(dclose(&did));                                                                                                     
%let rc=%sysfunc(filename(fileref));                                                                                                  
%mend;

It’s also possible to use this simple piece of SAS-code found below.

filename Macros "<Path>";

%inc Macros("*.sas");

Get execution information from SAS

The code below makes a print to your log containing different information related to your current SAS-session.
The code is heavily inspired from this article.

/* The macro need to know if your running the program on a remote server. The default is that the program is running on a remote server. If that is the case, the macro gets some information from the remote server. */ %macro GetExecInfo(rsubmit=yes);

/* Removes the %put-statement below. Makes it possible to get a fine print in your log. */ 
options nosource;

/* The programname and path. */
%let SASProgram = %sysget(sas_execfilepath);

/* If your running SAS on a remote server your need to supply the remote SAS with information about the local program being executed.*/ 
%if %upcase(&RSubmit) = YES %then 
%do; 
%syslput SASProgram = &SASProgram; 
%end;

%if %upcase(&RSubmit) = YES %then 
%do; 
rsubmit; 
%end;

%macro GetInfo; /* AIX (UNIX) systems and probably other systems doesn't have the same environment variables as eg Windows servers. */ 
%if &sysscpl = AIX %then 
%do; 
%let ServerName = N/A; 
/* It is possible to try the statements below to get the servername from an AIX-server. */ 
/*    host = sysget('HOST'); 
- OR - filename h pipe 'hostname'; 
data _null_; 
infile h; 
input; 
put _infile_; 
run; 
*/ 
%end; 
%else 
%do; 
%let ServerName = %sysget(computername); 
%end;

/* Inserts blank lines in the log. */ 
skip; 
skip; 
%put ******************************************************************;
%put *      Program : &SASProgram (Revision: ); 
%put *      Date : &sysday, &sysdate9; 
%put *      User : &sysuserid; 
%put *      Server : &ServerName; 
%put *      OS version : &sysscp (&sysscpl); 
%put *      SAS version : &sysvlong4; 
%put ******************************************************************; skip; 
skip; 
%mend; 

%GetInfo;

%if %upcase(&RSubmit) = YES %then 
%do; 
endrsubmit; 
%end;

options source; 
%mend;

%GetExecInfo(rsubmit=yes);

Options in SASv9.cfg

The options below can be used to congfigure SAS through the file SASv9.cfg
The file is usually located in c:\program files\SAS\SASFoundation\9.2\nls\en\.
NB! Depending on your version of SAS.
You can find the installation directory for SASv9.cfg through the SAS GUI. Go to Tools -> System Options.

Option	Description
-WORK w:\saswork\!username	If the username for the users using SAS is eg APM this options creates a Work directory under w:\saswork\ called APM – w:\saswork\APM\. This makes it easier for the users to retrieve the tables in the work-directory and easier for system administration to locate users that takes up a lot of storage on the work-drive.
-odsdest=listing	If you don’t like the default HTML-output. This option sets the output destination to listing. The output from SAS will now be shown like it used to.
-odsgraphics=off	If you don’t like the default HTML-output. This option will turn off ODS Graphics. The output from SAS will now be shown like it used to.
-MEMSIZE 4G	Sets the maximum aout of RAM/memory that can be used during a SAS-session. In this case 4GB.
-SORTSIZE 2G	Sets the maximum amount of RAM/memory that SORT procedure can use during a SAS-session. In this case 2GB.

Random sample of dataset in SAS

The code below will give you a random sample of a large dataset. The random sample will contain 25% randomly selected rows from the large dataset.

data Libname.RandomDataset;
set Libname.LargeDataset;
if ranuni(123456789) <= .25;
run;

Getting accessible schemas for a user

The code below gets the schemas from a Microsoft SQL-server that is accessible for a given user. It is made with SAS-code but the SQL-statement can be used regardless of using SAS.

proc sql noprint;
connect to odbc(datasrc=&_datasrc user=Your user; pwd="Your password");
select schema_name into :_schema_names separated by " "
from connection to odbc
(
select s.name as schema_name
from sys.schemas as s
inner join
sys.database_permissions as dbp
on s.schema_id=dbp.major_id
inner join
sys.sysusers as u
on
u.uid=dbp.Grantee_principal_id
where dbp.Class_desc='SCHEMA' and lower(u.name) = %str(%')Your domain\&uid.%str(%')
);

%put &_schema_names;
disconnect from odbc;
quit;

Loop through datasets in SAS

This code was found here. It lets you loop through datasets in a SAS-library and tries to print the first 10 observations from each dataset in the SAS-library.

proc contents data=<Your library>._all_ noprint out=temp (keep=memname);
run;

proc sort data=temp nodupkey;
     by memname;
run;

data _null_;
	set temp;
	call execute ('proc print data='|| memname || '(obs=10); title "*** ' || trim(memname) || ' ***"; run;');
run;

Call Execute lets you run PROC-statements in a datastep. It will simply execute the proc-statement after finishing the datastep. It is also possible to use ‘if‘, ‘then‘ and ‘else‘ in the datastep to execute certain parts of the code based on some value.
This is of course also possible through macro-code.