Ryslander.com – Page 8 – implementation, consolidation and innovation

Using SAS display manager (DM) for data exploration

The display manager (also known as DM) in base-SAS can be used for data exploration. For this demonstration the dataset sashelp.class will be used. Now sashelp.class is easy to get an overview of because it only has five variables. But if you have a lot of variables then some kind of data exploration/data manipulation might be handy.

Let’s say that you would like to take a look at the varable Age. In the DM you will write keep age.

This will result in a displaying of the data only showing the variable age. Now you would like to have all the other variables shown but keep age as the first variable being displayed. This can be done writing the command unide _all_ in the DM.

Now all the variable will be shown with age as the first variable.

It is also possible to keep multiple variables. In the DM you can eg. write keep ‘height weight’. Remember that when keeping multiple variables you will have to write the variables in ‘ ‘.

Now only these variables will be shown.

You can again unhide the rest of the variables writing unhide _all_ this will keep the height and weight variables as the first variables being shown.

Get SQL recipe for a dataset in SAS

The code below will make a file class.sql containing the SQL-code for creating the dataset sashelp.class

ods tagsets.sql file="class.sql";
 proc print data=sashelp.class ;
 run;
ods _all_ close;

The file will look something like this

Create table CLASS
(Name varchar(7), Sex varchar(1), Age float, Height float, Weight float);
Insert into CLASS(Name, Sex, Age, Height, Weight)
Values (‘Alfred’, ‘M’, 14, 69.0, 112.5);
Insert into CLASS(Name, Sex, Age, Height, Weight)
Values (‘Alice’, ‘F’, 13, 56.5, 84.0…

Making an empty dataset in SAS

The code below shows you how to make an empty dataset in SAS.
If you omit the if-sentence and below, then you will get an empty row in the dataset.

data StatusTable;
 length Dataset $100 Message $150 Status $10;
 call missing(Dataset, Message, Status);
 if _N_ = 0 then output;
 stop;
run;

This can also be done a bit easier in SQL.

proc sql noprint;
create table Dataset
(
Dataset char 100,
Message char 150,
Status char 10
);
quit;

Comparing datasets in SAS

The code below compares two datasets. It merges them together and makes three datasets. One dataset contains identical observations from the two datasets. The second dataset contains observations only found in one dataset. And the third dataset contains observations only found in the other dataset.

data InBoth InOne InTwo;
merge One (in=a) Two (in=b);
by <variables>;
if a and b then
 output InBoth;
if a and not b then
 output InOne;
if b and not a then
 output InTwo;
run;

A lot of good SAS macros

The link below has a lot of good SAS macros.

http://sas.cswenson.com/

Return value from SAS macro

The code below shows how to return a value from a SAS macro.

/*
Returns 0 if a specific variable is not found in a dataset and 1 if it is found.
*/
%macro VarExist(_Dataset=, _Variable=);
  %local dsid rc ;
  %let dsid = %sysfunc(open(&_Dataset));

  %if (&dsid) %then %do;
     %if %sysfunc(varnum(&dsid,&_Variable)) %then 1;
     %else 0 ;
     %let rc = %sysfunc(close(&dsid));
  %end;
  %else 0;
%mend VarExist;

/*
Go to do something if the dataset sashelp.class contains the variable
age - and it does.
*/
%if %VarExist(_Dataset=sashelp.class, _Variable=age) %then
%do;
/* Something */
%end;

It is also possible to return a value from a macro using the code below. This only works for simple macros.

%macro add(no1=, no2=);
 %let result = %eval(&no1 + &no2);

 &Result
%mend;

%let sum = %add(no1=2, no2=2);

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