Technology Tales

ERROR: Ambiguous reference, column xx is in more than one table.

Sometimes, SAS messages are not all that they seem, and a number of them are issued from PROC SQL when something goes awry with your code. In fact, I got a message like the above when ordering the results of the join using a variable that didn't exist in either of the datasets that were joined. This type of thing has been around for a while (I have been using SAS since version 6.11, and it was there then) and it amazes me that we haven't seen a better message in more recent versions of SAS; it was SAS 9.2 where I saw it most recently.

proc sql noprint;
    select a.yy, a.yyy, b.zz
        from a left join b
            on a.yy=b.yy
                order by xx;
quit;

Creating placeholder graphics in SAS using PROC GSLIDE for when no data are available

Recently, I found myself with a plot to produce, but there were no data to be presented, so a placeholder output was needed. For a listing or a table, this is a matter of detecting if there are observations to be listed or summarised and then issuing a placeholder listing using PROC REPORT if there are no data available. Using SAS/GRAPH, something similar can be achieved using one of its curiosities.

In the case of SAS/GRAPH, PROC GSLIDE looks like the tool to user for the same purpose. The procedure does get covered as part of a SAS Institute SAS/GRAPH training course, but they tend to gloss over it. After all, there is little reason to go creating presentations in SAS when PowerPoint and its kind offer far more functionality. However, it would make an interesting tale to tell how GSLIDE became part of SAS/GRAPH in the first place. Its existence makes me wonder if it pre-exists the main slideshow production tools that we use today.

The code that uses PROC GSLIDE to create a placeholder graphic is as follows (detection of the number of observations in a SAS dataset is another entry on here):

proc gslide;
    note height=10;
    note j=center "No data are available";
run;
quit;

PROC GSLIDE is one of those run group procedures in SAS so a QUIT statement is needed to close it. The NOTE statements specify the text to be added to the graphic. The first of these creates a blank line of the required height for placing the main text in the middle of the graphic. It is the second one that adds the centred text that tells users of the generated output what has happened.

Smoother use of more than one SAS DMS session at a time

Unless you have access to SAS Enterprise Guide, being able to work on one project at a time can be a little inconvenient. It is possible to open up more than one Display Manager System (DMS, the traditional SAS programming interface) session at a time only to get a pop-up window for SAS documentation for the second and subsequent sessions. You don't get your settings shared across them, either, while also losing any changes to session options after shutdown.

The cause of both of the above is the locking of the SASUSER directory files by the first SAS session. However, it is possible to set up a number of directories and set the -sasuser option to point at different ones for different sessions.

On Windows, the command in the SAS shortcut becomes:

C:\Program Files\SAS\SAS 9.1\sas.exe -sasuser "c:\sasuser\session 1\"

On UNIX or Linux, it would look similar to this:

sas -sasuser "~/sasuser/session1/"

Since the "session1" in the folder paths above can be replaced with whatever you need, you can have as many as you want too. It might not seem much of a need but synchronising the SASUSER folders every now and again can give you a more consistent set of settings across each session, all without intrusive pop up boxes or extra messages in the log too.

Dealing with variable length warnings in SAS 9.2

A habit of mine is to put a LENGTH or ATTRIB statement between DATA and SET statements in a SAS data step to reset variable lengths. By default, it appears that this triggers truncation warnings in SAS 9.2 or SAS 9.3 when it didn't in previous versions. SAS 9.1.3, for instance, allowed you to have something like the following for shortening a variable length without issuing any messages at all:

data b;
    length x $100;
    set a;
run;

In this case, x could have a length of 200 previously and SAS 9.1.3 wouldn't have complained. Now, SAS 9.2 and 9.3 will issue a warning if the new length is less than the old length. This can be useful to know, but it can be changed using the VARLENCHK system option. Though the default value is WARN, it can be set to ERROR if you really want to ensure that there is no chance of truncation. Then, you get error messages and the program fails where it normally would run with warnings. Setting the value of the option to NOWARN restores the type of behaviour seen in SAS 9.1.3 and versions before that.

The SAS documentation says that the ability to change VARLENCHK can be restricted by an administrator, so you might need to deal with this situation in a more locked down environment. Then, one option would be to do something like the following:

data b;
    drop x;
    rename _x=x;
    set a;
    length _x $100;
    _x=strip(x);
run;

While It's a bit more laborious than setting the VARLENCHK option to NOWARN, the idea is that you create a new variable of the right length and replace the old one with it. That gets rid of warnings or errors in the log and resets the variable length as needed. Of course, you have to ensure that there is no value truncation with either remedy. If any is found, then the dataset specification probably needs updating to accommodate the length of the values in the data. After all, there is no substitute for getting to know your data and doing your own checking should you decide to take matters into your hands.

There is a use for the default behaviour, though. If you use a specification to specify a shell for a dataset, then you will be warned when the shell shortens variable lengths. That allows you to either adjust the dataset or your program. Also, it provides additional information when you get variable length mismatch warnings when concatenating or merging datasets. There was a time when SAS wasn't so communicative in these situations and some investigation was needed to establish which variable was affected. Now, that has changed without leaving the option to work differently if you so do desire. Sometimes, what can seem like an added restriction can have its uses.

Setting VIEWTABLE to show column names in SAS

The following is the default behaviour 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. While the SAS EG dataset viewing tool may appear like the former of these, 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 by issuing a command like the following:

VT SASHELP.VCOLUMN COLHEADING=NAMES

Above VT is the VIEWTABLE shortcut, while 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 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 interesting new features for programmers, so it appears 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.

Creating a Data Set Containing Confidence Intervals Using PROC UNIVARIATE

While you could generate data sets containing means and confidence intervals using PROC SUMMARY or PROC MEANS, curiosity and the need to verify a program using a different technique were what drove me to consider using PROC UNIVARIATE for the task. For the record, the PROC SUMMARY code is below and the only difference between it and MEANS is that it doesn't produce output by default, something that's not needed in this case anyway. Quite why there are two SAS procedures doing the same thing is beyond me, though I do wonder if the NOPRINT option was a later addition than these two procedures. The LCLM and UCLM keywords are what triggers the calculation of confidence limits and the ALPHA option controls the confidence interval used; 0.05 specifies a 95% interval, 0.1 a 90% one and so on.

proc summary data=sashelp.class mean lclm uclm alpha=0.05;
    var age;
    output out=sasuser.lims mean=mean lclm=lclm uclm=uclm;
run;

Given that I have had PROC UNIVARIATE producing statistics that MEANS/SUMMARY didn't in previous versions of SAS (I believe that it was standard deviation that was absent from MEANS/SUMMARY), I might have expected the calculation and export of confidence limits to a data set to be straightforward. Sadly, it's not a case of simply adding LCLM and UCLM keywords in the OUTPUT statement for the procedure, and ODS OUTPUT is needed to create the data set instead. An ODS SELECT statement is needed to pick out the BasicIntervals output object (UNIVARIATE creates quite a few, it seems) that is created through specification of the CIBASIC and ALPHA (performs the same role as it does for PROC MEANS/SUMMARY) options on the PROC UNIVARIATE statement. The reason for the ODS LISTING and ODS RTF statements below is to stop output being sent to the output window in a standard SAS session. For some reason, it appears that you need the sending of output to one of the LISTING, HTML or RTF destinations or there will be no data in the data set; I met up with the same behaviour when using ODS PS, an ODS PRINTER destination. The data set will contain statistics for mean, standard deviation and variance, so that's why there is a WHERE clause on the ODS OUTPUT statement.

ods listing close;
ods rtf body="c:\temp\uni_eg.doc";
ods select BasicIntervals;
ods output BasicIntervals=sasuser.stats(where=(lowcase(parameter)="mean") );

proc univariate cibasic alpha=0.05 data=sashelp.class;
    var age;
run;

ods output close;
ods rtf close;
ods listing;

Using ODS Graphics to Create Plots Using PROC LIFETEST

One of the nice things about SAS 9.2 is that creation of statistical graphics is enhanced using the Output Delivery System (ODS). One of the beneficiaries of this is PROC LIFETEST, a procedure that gained a lot when data sets could be created from it using ODS OUTPUT statements. Before that, it was a matter of creating text output and converting it to a SAS data set using Data Step, and that was a nuisance on a system that attached special significance to output destinations set up using PROC PRINTTO. What you’ll find below is a sample of the type of code for creating a Kaplan-Meier survival plot for time to adverse events resulting in discontinuation of study treatment, with actual and censored times. The IMAGENAME parameter on the ODS GRAPHICS statement line controls the name of the file, and it is possible to change the type using the IMAGEFMT parameter too.

ods graphics on / imagename=”fig5″;
proc lifetest data=km3 method=km plots=survival;
    time timetoae*cens_ae(0);
run;
ods graphics off;

ERROR 22-322: Syntax error, expecting one of the following: a name, *.

This is one of the classic SAS errors that you can get from PROC SQL, and it can be thrown by a number of things. Missing out a comma in a list of variables in a SELECT statement is one situation that will do it, as will be having an extraneous one. As I discovered recently, an ill-defined SAS function nesting like LEFT(TRIM(PERIOD,BEST.)) will have the same effect; notice the missing PUT function in the example. The latter surprised me because I might have expected something more descriptive for this, as would be the case in data step code. In the event, it took some looking before the problem hit me because it's remarkable how blind you can become to things that are staring you in the face. Familiarity really can make you pay less attention.

Reading data into SAS using the EXCEL and PCFILES library engines

Recently, I had the opportunity to have a look at the Excel library engine again because I need to read Excel data into SAS. You need SAS Access for PC Files licensed for it to work, but it does simplify the process of getting data from spreadsheets into SAS. It all revolves around setting up a library pointing at the Excel file using the Excel engine. The result is that every worksheet in the file is treated like a SAS dataset, even if their names contain characters that SAS considers invalid for dataset names. The way around that is to enclose the worksheet name in single quotes with the letter n straight after the closing quote, much in the same way as you'd read in text strings as SAS date values ('04MAR2010'd, for example). To make all of this clearer, I have added some example code below.

libname testxl excel 'c:\test.xls';

data test;
    set testxl.'sheet1$'n;
run;

All of the above does apply to SAS on Windows (I have used it successfully in 9.1.3 and 9.2) but there appears to be a way of using the same type of thing on UNIX too. Again, SAS Access for PC Files is needed as well as a SAS PC Files server on an available Windows machine, and it is the PCFILES engine that is specified. While I cannot say that I have had the chance to see it working in practice but seeing it described in SAS Online Documentation corrected my previous misimpressions about the UNIX variant of SAS and its ability to read in Excel or Access data. Well, you learn something new every day.

Working with the ODS templates and styles when batch processing

I ran into some trouble with creating new templates and styles while running a SAS job in batch mode. The cause was the user of the RSASUSER switch on the SAS command. This sets the SASUSER library to be read-only, and that is what is used to store new templates and styles by default. The fix is to switch these to another library to which there is write-access, WORK, for example. Here's the line of code that achieves the manoeuvre:

ODS PATH work.templat(update) sasuser.templat(read) sashelp.tmplmst(read);

Apparently, the same change might be needed for stored processes too, so it's one to keep in mind.