topic Re: Why am I getting _LABEL_ column after PROC TRANSPOSE in SAS Programming

Why am I getting _LABEL_ column after PROC TRANSPOSE

Nietzsche — Sun, 04 Dec 2022 10:09:07 GMT

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.

Re: Why am I getting _LABEL_ column after PROC TRANSPOSE

Rick_SAS — Sun, 04 Dec 2022 11:28:35 GMT

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.

Re: Why am I getting _LABEL_ column after PROC TRANSPOSE

Nietzsche — Sun, 04 Dec 2022 19:36:48 GMT

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?

Re: Why am I getting _LABEL_ column after PROC TRANSPOSE

Rick_SAS — Mon, 05 Dec 2022 00:39:27 GMT

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;

Re: Why am I getting _LABEL_ column after PROC TRANSPOSE

Tom — Mon, 05 Dec 2022 00:46:41 GMT

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

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