Technology Tales

Some SAS Macro code for detecting the presence or absence of a variable in a dataset

Recently, I needed to put in place some code to detect the presence or absence of a variable in a dataset and I chose SAS Macro programming as the way to do what I wanted. The logic was based on a SAS sample that achieved the same result in a data step and some code that I had for detecting the presence or absence of a dataset. Mixing the two together gave me something like the following:

%macro testvar(ds=,var=);

%let dsid=%sysfunc(open(&ds,in));
%let varexist=%sysfunc(varnum(&dsid,&var));
%if &dsid > 0 %then %let rc=%sysfunc(close(&dsid));

%if &varexist gt 0 %then %put Info: Variable &var is in the &ds dataset;
%else %put Info: Variable &var is not in the &ds dataset;

%mend testvar;

%testvar(ds=dataset,var=var);

What this does is open up a dataset and look for the variable number in the dataset. In datasets, variables are numbered from left to right with 1 for the first one, 2 for the second and so on. If the variable is not in the dataset, the result is 0 so you know that it is not there. All of this is what the VARNUM SCL function within the SYSFUNC macro function does. In the example, this resolves to %sysfunc(varnum(&dsid,var)) with no quotes around the variable name like you would do in data step programming. Once you have the variable number or 0, then you can put in place some conditional logic that makes use of the information like what you see in the above simple example. Of course, that would be expanded to something more useful in real life but I hope it helps to show you the possibilities here.

Setting VIEWTABLE to show column names in SAS

By default in the DMS, Base SAS opens datasets from its Explorer using VIEWTABLE and with variable labels in the column headings and not variable names. Because I have been fortunate to use systems with SAS/FSP both installed and licensed, I have taken to using FSVIEW for browsing SAS datasets as a workaround and, though the interface may look old to some, it proves to be a very flexible tool that still has a few things to teach newer ones. With SAS Enterprise Guide, the dataset viewing functionality is different to both VIEWTABLE and FSVIEW but I have been to make it work for me. The SAS EG dataset viewing tool may appear like the former of these but it has a few tricks to teach its forbear.

Now that I find myself working again with the traditional SAS DMS interface and without SAS/FSP, I decided to see if there was a way to get VIEWTABLE to display variable names instead of variable labels by default. As it happened, the answer was found in an internet forum discussion. From the SAS command line, you can achieve the result issuing a command like the following:

VT SASHELP.VCOLUMN COLHEADING=NAMES

VT is the VIEWTABLE shortcut but it is the COLHEADING=NAMES option on the line that gets variable names shown in column headings. Taking it further, you can set this as the default setting for datasets opened using a mouse from Explorer panes using the following procedure:

• Click in or on the Explorer pane to highlight the the Explorer window.
• Select Tools->Options->Explorer in the menus.
• Select the Members tab.
• Double click on the TABLE icon.
• Double click on the &Open action.
• Set the Action command to:  VIEWTABLE %8b.’%s’.DATA COLHEADING=NAMES.
• Click on the Set Default button.
• Save changes and close the Explorer Options window.

Because the DMS looks similar across versions 8.0 through to 9.2, the above instructions should be relevant to all of those. While I have yet to get the opportunity to use SAS 9.3, I would be surprised to find that the traditional SAS interface has changed there too, even though much else has changed about SAS. In fact, the latest version of SAS has brought quite a few new interesting features for programmers so it seems that you can do more through a familiar interface, not entirely a bad thing. It looks as if this VIEWTABLE tweak could be useful for a while yet.

A few more SAS functions to know

There are whole pile of SAS functions for testing text strings that hadn’t come to my attention until this week. Until then, I’d have gone about using functions like INDEX and PRXMATCH functions for the same sort of ends but it’s never any load to have a few different ways of doing things and to use the right one for the job. Here’s a quick list of my recent discoveries:

ANYALNUM: First position of any alphanumeric character, returns 0 if absent

ANYALPHA: First position of any alphabetic character (letter of the alphabet), returns 0 if absent

ANYCNTRL: First position of any control character, returns 0 if absent

ANYDIGIT: First position of any numeric character, returns 0 if absent

ANYFIRST: First position of any character that can be used as the start of a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent

ANYGRAPH: First position of any printable character that isn’t white space, returns 0 if absent

ANYLOWER: First position of any lowercase letter, returns 0 if absent

ANYNAME: First position of any character that can be used in a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent

ANYPRINT: First position of any printable character, returns 0 if absent

ANYPUNCT: First position of any punctuation character, returns 0 if absent

ANYSPACE: First position of any whitespace character (tabs, carriage returns and the like), returns 0 if absent

ANYUPPER: First position of any uppercase letter, returns 0 if absent

ANYXDIGIT: First position of any hexadecimal character, returns 0 if absent

NOTALNUM: First position of any non-alphanumeric character, returns 0 if absent

NOTALPHA: First position of any non-alphabetic character, returns 0 if absent

NOTCNTRL: First position of anything that isn’t a control character, returns 0 if absent

NOTDIGIT: First position of any non-numeric character, returns 0 if absent

NOTFIRST: First position of any character that cannot be used as the start of a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent

NOTGRAPH: First position of anything that isn’t a printable character that isn’t white space, returns 0 if absent

NOTLOWER: First position of anything that isn’t a lowercase letter, returns 0 if absent

NOTNAME: First position of any character that cannot be used in a SAS variable name when VALIDVARNAME is set to V7, returns 0 if absent

NOTPRINT: First position of any non-printable character, returns 0 if absent

NOTPUNCT: First position of anything that isn’t a punctuation character, returns 0 if absent

NOTSPACE: First position of anything that isn’t a whitespace character, returns 0 if absent

NOTUPPER: First position of anything that isn’t an uppercase letter, returns 0 if absent

NOTXDIGIT: First position of anything that isn’t a hexadecimal character, returns 0 if absent

Apart from simpler cases where other techniques would work well with the a similar amount of effort, there are others that would need some investigation if you were program them without using one of the above functions. For that reason, I’ll be keeping them in mind for when I might meet one of those more complex scenarios.