ERROR: This range is repeated, or values overlap: – .

This is another posting in an occasional series on SAS error and warning messages that aren’t as clear as they’d need to be. What produced the message was my creation of a control data set that I then wished to use to create a data-driven (in)format. It was the PROC FORMAT step that issued the message and I got no (in)format created. However, there were no duplicate entries in the control data set as the message suggested to me so a little more investigation was needed.

What that revealed was that there might be one variable missing from the data set that I needed to have. The SAS documentation has FMTNAME, START and LABEL as compulsory variables with they containing the following: format name, initial value and displayed value. My intention was to create a numeric code variable for one containing character strings using my data-driven format with then numbers specified within a character variable as it should be. What was missing then was TYPE.

This variable can be one of the following values: C for character formats, I for numeric informats, J for character informats, N for numeric formats and P for picture formats. Due to it being a conversion from character values to numeric ones, I set the values of TYPE to I and used an input function to do the required operations. The code for successfully creating the informat is below:

proc sql noprint;
create table tpts as
select distinct “_vstpt” as fmtname,
“I” as type,
vstpt as start,
vstpt as end,
strip(put(vstptnum,best.)) as label
from test
where not missing(vstptnum);

proc format library=work cntlin=tpts;

Though I didn’t need to do it, I added an END variable too for sake of completeness. In this case, the range is such that its start and end are the same and there are cases where that will not be the case though I am not dwelling on those.

WARNING: Engine XPORT does not support SORTEDBY operations. SORTEDBY information cannot be copied.

When recently creating a transport file using PROC COPY and the XPORT library engine, I found the above message in the log. The code used was similar to the following:

libname tran xport “c:\temp\tran.xpt”;

proc copy in=data out=tran;

When I went seeking out the cause on the web, I discovered this SAS Note that dates from before the release of SAS 6.12, putting the issue at more than ten years old. My take on its continuing existence is that we still to use a transport file format that was introduced in SAS 5.x for sake of interoperability, both between SAS versions and across alternatives to the platform.

The SORTEDBY flag in a dataset header holds the keys used to sort the data and it isn’t being copied into the XPORT transport files, hence the warning. To get rid of it, you need to remove the information manually in data step using the SORTEDBY option on the DATA statement or using PROC DATASETS, which avoids rewriting the entire data set.

First up is the data step option:

data test(sortedby=_null_);
set sashelp.class;

Then, there’s PROC DATASETS:

proc datasets;
modify test(sortedby=_null_);

It might seem counterproductive to exclude the information but it makes no sense to keep what’s being lost anyway. So long as the actual sort order is unchanged, and I believe that the code that that below will not alter it, we can live with its documentation in a specification until transport files created using PROC CPORT are as portable as those from PROC COPY.

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;

Calculating geometric means using SAS

Recently, I needed to calculate geometric means after a break of a number of years and ended racking a weary brain until it was brought back to me. In order that I am not in the same situation again, I am recording it here and sharing it always is good too.

The first step is to take the natural log (to base e or the approximated irrational value of the mathematical constant, 2.718281828) of the actual values in the data. Since you cannot have a log of zero, the solution is either to exclude those values or substitute a small value that will not affect the overall result as is done in the data step below. In SAS, the log function uses the number e as its base and you need to use the log10 equivalent when base 10 logs are needed.

data temp;
set temp;
if result=0 then _result=0.000001;
else _result=result;

Next, the mean of the log values is determined and you can use any method of doing that so long as it gives the correct values. PROC MEANS is used here but PROC SUMMARY (identical to MEANS except it defaults to data set creation while that generates output by default; because of that, we need to use the NOPRINT option here), PROC UNIVARIATE or even the MEAN function in PROC SQL.

proc means data=temp noprint;
output out=mean mean=mean;
var ln_result;

With the mean of the log values obtained, we need to take the exponential of the obtained value(s) using the EXP function. This returns values of the same magnitude as in the original data using the formula emean.

data gmean;
set mean;

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 is needed. For a lisitng or a table, this is a matter of detecting if there are observations to be listed or summarised and then issuing a placeholder lisitng using PROC REPORT if there are no data available. Using SAS/GRAPH, something similar can be acheived 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”;

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.