SAS PROC Means with StackODSOutput: A Variable called "Label"

Question

I have a strange problem with PROC Means and using the StackODSOutput option. Consider this example.

I first create a dummy dataset for analysis.

/* Step-1: Create a dummy dataset for analysis */
data ds1;
    label x = 'Variable X';
    label y = 'Variable Y';
    do i = 1 to 100;
        x = ranuni(1234);
        y = ranuni(5678);
        keep x y;
        output;
    end;
run;

Then, I run a PROC MEANS with the StackODSOutput option. This creates an output dataset called "stats".

/* Step-2: I run PROC means to capture the output in a dataset called stats */
proc means data=ds1 StackODSOutput mean;
    var x y;
    ods output summary=stats;
run;

This "stats" dataset has a variable called "Label". I know that the variable exists because I do a proc contents and I see the variable there.

/* Step-3: Confirm visually that there is a variable called Label in stats dataset */
proc contents data=stats varnum; run;

However, I cant seem to reference this variable called "Label" anywhere. For instance, the following PROC SQL statement generates an error. I am able to reference all other variables in the "Stats" dataset without any issues.

/* Step-4: But, I cannot seem to reference the variable called "Label" in stats dataset! */
proc sql;
    select Variable, Label from stats; 
quit;

The error is as follows:

43         proc sql; 
44          select Variable, Label from stats; 
ERROR: The following columns were not found in the contributing tables: Label. 
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 
45         quit;

Do you know if I am doing something wrong? Is something wrong with my SAS code or SAS installation?

MY SAS version is SAS 9.3 (9.03.01M2P08152012).

Thanks.

Karthik.

As per Reeza's request, here is the full log output.

1                                                          The SAS System                          15:52 Wednesday, November 9, 2016

1          %_eg_hidenotesandsource;
5          %_eg_hidenotesandsource;
20         
21         /* Step-1: Create a dummy dataset for analysis */


22         data ds1;
23          label x = 'Variable X';
24          label y = 'Variable Y';
25          do i = 1 to 100;
26              x = ranuni(1234);
27              y = ranuni(5678);
28              keep x y;
29              output;
30          end;
31         run;

NOTE: The data set WORK.DS1 has 100 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


32         
33         /* Step-2: I run PROC means to capture the output in a dataset called stats */
34         proc means data=ds1 StackODSOutput mean;
35          var x y;
36          ods output summary=stats;
37         run;

NOTE: The data set WORK.STATS has 2 observations and 3 variables.
NOTE: There were 100 observations read from the data set WORK.DS1.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.06 seconds
      cpu time            0.03 seconds


38         
39         /* Step-3: Confirm visually that there is a variable called Label in stats dataset */
40         proc contents data=stats varnum; run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


41         
42         /* Step-4: But, I cannot seem to reference the variable called "Label" in stats dataset! */
43         proc sql;
44          select Variable, Label from stats;
ERROR: The following columns were not found in the contributing tables: Label.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
45         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

2                                                          The SAS System                          15:52 Wednesday, November 9, 2016

46         /* What! */
47         
48         
49         
50         %_eg_hidenotesandsource;
62         
63         
64         %_eg_hidenotesandsource;
67         

Answer 1

I have the same issue as you when running the code. I have SAS 9.4 and am running on Linux. Here's my evaluation of the issue at my end:

data _NULL_;
  set stats;
  put _all_;
run;

Shows that the variable name for 'Label' is not quite as it seems:

22         data _NULL_;
23           set stats;
24           put _all_;
25         run;

Variable=x Label   =Variable X Mean=0.461116 _ERROR_=0 _N_=1
Variable=y Label   =Variable Y Mean=0.525342 _ERROR_=0 _N_=2

Notice the spaces between the variable name 'Label' and the equals character. None of the other variables look like this. Maybe the name of the variable is corrupted.

Load the variable names from the dictionary.columns table into another table and look at the values:

proc sql;
  create table x as
  select name as nm from dictionary.columns
  where libname = 'WORK' and memname = 'STATS';
quit;

data _NULL_;
  set x;
  put nm= nm $hex32.;
run;

The $HEX32. format translates the text into ASCII codes, so you can see if there are any unprintable chars in there. The output of this datastep is:

22         data _NULL_;
23           set x;
24           put nm= nm $hex32.;
25         run;

nm=Variable 5661726961626C652020202020202020
nm=Label    4C6162656C0000002020202020202020
nm=Mean 4D65616E202020202020202020202020

Looking at the Label variable, firstly the gap still appears between it and the next output. The hex code contains a few repeated zeroes:

4C6162656C0000002020202020202020

4C=L
61=a
62=b
65=e
6C=l
00=?
20=<space>

So it's these ASCII zeroes in the 'Label' variable name that are causing the problem. SAS can only display this as 'Label ', where these ASCII zeroes (a.k.a ASCII NULLs) appear as spaces.

Fix

I'm not aware of a way to refer to columns that contain ASCII special chars, so what we can do is rename the column. However, we still can't refer to 'Label ' by name, so we need to refer to it indirectly. One way is using an array:

data stats_fix;
  set stats;
  array c{*} _CHARACTER_;
  var=c[1];
  Label=c[2];
run;

Looking at the output dataset is quite strange. The dataset has two variables called 'Label'. We know that one is 'Label' the other is 'Label000000'.

It might be worth raising this with SAS Tech Support as a bug in PROC MEANS, feel free to use as much of this answer as you like.

Answer 2

This is a bug. It creates a variable name with trailing blanks, which is possible in EG if validvarname is set to ANY.

Fix:

Option validvarname=V7;

https://communities.sas.com/t5/SAS-Enterprise-Guide/SAS-EG-won-t-recognize-a-variable-that-has-the-name-quot-Label/m-p/294936