topic Re: Why does proc import not import column labels from xls ? in SAS Programming

Why does proc import not import column labels from xls ?

inquistive — Mon, 11 Jul 2022 18:55:44 GMT

Hi experts,

I have an xls worksheet as follows:

Upon export this is the output:

My question is- why is SAS unable to import the column label (for the COMMENTS column) when I click that particular cell, only the COMMENTS is seen on the bar above .All others are displayed in full. Any idea what is the problem with this cell?

All others are okay.

I believe the cell format is causing SAS not to import all info from that cell. Please advise what troubleshooting steps are required to import the column label(s)( Not sure if this is the right term though).

Re: Why does proc import not import column labels from xls ?

Kurt_Bremser — Mon, 11 Jul 2022 19:00:40 GMT

I see nothing SAS-y here, only Excel screenshots. Have you actually done anything in SAS?

Re: Why does proc import not import column labels from xls ?

inquistive — Mon, 11 Jul 2022 19:06:28 GMT

proc import out= test_tmp
			datafile="C:\Users\test.xls"
            dbms =xls replace;
            getnames= yes;
run;

proc export data= test_tmp
			outfile = "C:\Users\test2.xls"
			dbms = excel replace;
			sheet = 'test1';  
run;

Re: Why does proc import not import column labels from xls ?

Kurt_Bremser — Mon, 11 Jul 2022 19:11:25 GMT

The string

COMMENTS FULL TIME STUDENT OR WORKING?

is too long for a SAS name (maximum 32 characters), so it is shortened by PROC IMPORT.

Re: Why does proc import not import column labels from xls ?

Reeza — Mon, 11 Jul 2022 19:20:38 GMT

Comments looks like it has an new line/end of line character in that field which means it would truncate at the end of the line.

Usually that gets entered into Excel when you hit ALT+ENTER in a cell.

Re: Why does proc import not import column labels from xls ?

Tom — Mon, 11 Jul 2022 19:25:20 GMT

Not sure why you are not seeing the whole name.

What tool did you use to view the dataset so that you could take that photograph you pasted?

What does PROC CONTENTS show?

Remember that the NAME of a variable can only be 32 bytes long. And unless you have (accidentally?) set the VALIDVARNAME option to ANY it cannot include spaces and other special characters. But the original header cell value should be stored in the LABEL of the variable.

Example:

6    proc import datafile='c:\downloads\example.xls' dbms=xls out=test replace;
7    run;

NOTE:    Variable Name Change.  Space in name -> Space_in_name
NOTE:    Variable Name Change.  Linebreak
in name -> Linebreak_in_name
NOTE:    Variable Name Change.  Name that is more than thirty-tw -> Name_that_is_more_than_thirty_tw
NOTE: The import data set has 1 observations and 4 variables.
NOTE: WORK.TEST data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


8
9    proc contents data=test varnum out=contents;
10   run;

NOTE: The data set WORK.CONTENTS has 4 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


11
12   proc print data=contents;
13     var varnum name type length label;
14   run;

NOTE: There were 4 observations read from the data set WORK.CONTENTS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds

                                             Variables in Creation Order

#    Variable                            Type    Len    Format     Label

1    ID                                  Num       8    BEST12.    ID
2    Space_in_name                       Num       8    BEST12.    Space in name
3    Linebreak_in_name                   Num       8    BEST20.    Linebreak
in name
4    Name_that_is_more_than_thirty_tw    Num       8    BEST12.    Name that is more than thirty-two characters long


Obs    VARNUM    NAME                                TYPE    LENGTH    LABEL

 1        1      ID                                    1        8      ID
 2        3      Linebreak_in_name                     1        8      Linebreak
in name
 3        4      Name_that_is_more_than_thirty_tw      1        8      Name that is more than thirty-two characters long
 4        2      Space_in_name                         1        8      Space in name

Re: Why does proc import not import column labels from xls ?

inquistive — Thu, 14 Jul 2022 20:13:36 GMT

Hi Reeza,
I found it to be exactly as you suspected- there are multiple lines entered after hitting ALT+ENTER. And the end users have done it by design, not by mistake. Is there a way to tell SAS to go to consecutive lines and read/fetch them(values)?

Re: Why does proc import not import column labels from xls ?

Tom — Thu, 14 Jul 2022 20:55:48 GMT

It should be doing that already. Did you run proc contents on the dataset? What does it show as the variable name? What does it show as the label?

You can use a RENAME statement to change the name of a variable.

You can use a LABEL statement to change the label attached to a variable.

You can fix it after the fact.

proc contents data=have noprint out=contents; run;
data fixes ;
  set have;
  where indexc(name||label,'0A0D'x);
  length newname $32 newlabel $256 ;
  newname = left(compbl(translate(name,'  ','0A0D'x)));
  newlabel = left(compbl(translate(label,'  ','0A0D'x)));
run;
proc sql noprint;
select
    case when (upcase(name) ne upcase(newname)) 
         then catx(' ',nliteral(name),nliteral(newname))
    else ' ' end
  , catx('=',nliteral(newname),quote(trim(label),"'"))
  into :rename separated by ' '
     , :label separated by ' '
  from fixed
;
quit;

data want ;
  set have;
  rename &rename ;
  label &label;
run;