Why am I getting _LABEL_ column after PROC TRANSPOSE

[Nietzsche]

Hi, I am trying follow the book example on PROC TRANSPOSE

I did the same thing, except first, I imported the class data from an excel file.

But you can see from the report that the imported is the same as the cert.class used in the book from the result.

After I transposed it and print out the result, I got an extra "_LABEL_" column after _NAME_ column in the transposed data.

What is that the case?

***************************************************************;
proc import datafile='~/spg/cert/class.xlsx'
    dbms=xlsx
    out=class
    replace;
    getnames=yes;
run;
***************************************************************;
proc print data=class; run;

proc transpose data=class out=transposed;run;

proc print data=transposed noobs; 
title 'scores for the year';
run;

I have attached the xlsx file if you wish to replicate the problem.

[Rick_SAS]

Both output are correct. The output you get depends on whether the original data set contains labels for the variables. 

When you import data from an Excel file, SAS automatically creates labels for each variable. I think that the output in the book is from a data set that does NOT contain labels.  Run the following example to see the difference:

title "Data set with NO labels";
data Class;
length Name $7;
input Name  Score1 Score2 Score3 Homework;
datalines;
LINDA 53 60 66 42
DEREK 72 64 56 32
KATHY 98 82 100 48
MICHAEL 80 55 95 50
;

proc transpose data=class out=transposed;
run;

proc print data=transposed noobs; 
run;

title "Data set with LABELS";
data Class2;
set Class;
label Name='Name' Score1='Score1' Score2='Score2' Score3='Score3' Homework='Homework';
run;

proc transpose data=class2 out=transposed;
run;

proc print data=transposed noobs; 
run;

So, your output is correct because your input data set contains labels. It doesn't match the book because they used data that did not have labels.

[Nietzsche]

okay so if SAS create labels during the import stage, how come _LABEL_ is not printed in PROC PRINT, but it is there after PROC TRANSPOSE?

Is there a way to suppress _LABEL_ from being printed in PROC PRINT?

[Rick_SAS · Accepted solution]

Sure. The easiest way is to DROP the _LABEL_ column:

proc print data=transposed(drop=_LABEL_) noobs; 
run;

Alternatively, you could use the VAR statement to explicitly name the variables that you want to print:

proc print data=transposed(drop=_LABEL_) noobs; 
var _NAME_ COL: ;
run;

[Tom]

You can also prevent the _LABEL_ columns from being created by using the system option NOLABEL.

options nolabel;
proc transpose .....
run;
options label;