Pound (#) Sign in Excel Column Name

Reply
New Contributor
Posts: 2

Pound (#) Sign in Excel Column Name

Hi people,

"Usage Note 16842: Writing SAS Variable Labels that Contain Dots to Excel Field/Column Names" explains how to get a dot in an Excel column name by using a replacement pound (#) sign.  This is then converted to a dot (.) during the export. 

I need to get an actual pound sign (#) in the column name  (e.g. ' Transaction # ' ) without it being converted to a dot (e.g. ' Transaction . ').

Does anyone know how to do this?

I will be creating the sheet in a similar way to the Usage Note 16842...

Kind regards,

Super User
Super User
Posts: 6,842

Re: Pound (#) Sign in Excel Column Name

I do not have any trouble creating column headers or sheet names that include # character when using the XLSX libname engine.

libname xyz xlsx 'c:\temp\xyz.xlsx' ;

options validvarname=any validmemname=extend ;

data xyz.'a#b'n; set sashelp.class;

  rename name='a#b'n ;

run;

libname xyz clear;

New Contributor
Posts: 2

Re: Pound (#) Sign in Excel Column Name

Thanks Tom. I tried this and I did not get your results.  I am using version 9.1 and cannot create .XLSX files but when I tried similar code I got the usual result i.e. (.) substituted for (#)

libname xyz 'c:\temp\xyz.xls' ;

option validvarname=any; *---option experimental in v9.1;

/*option validmemname=extend ; *--- option not valid for V9.1 */

data xyz.'a#b'n; set sashelp.class;

  rename name='a#b'n ;

run;

libname xyz clear;

This resulted in a sheet name of 'a.b' and a column name of 'a.b'

Kind regards,

Super User
Posts: 9,854

Re: Pound (#) Sign in Excel Column Name

How do you create the Excel file ?

Proc Report ?

If it was ,by default  # is the split character for variables' label , You need to change it to avoid to eat # .

proc report  split='~'

Super User
Super User
Posts: 7,668

Re: Pound (#) Sign in Excel Column Name

What version SAS are you using.  That note was for 9.1.  There are many ways to get data into Excel, Libname as Tom mentioned, tagsets.excelxp.  Maybe move your code to use one of those.

Super User
Super User
Posts: 6,842

Re: Pound (#) Sign in Excel Column Name

Sounds like you might need to upgrade your SAS version to be able to get this to work.  SAS 9.1 was released over 10 years ago.

Ask a Question
Discussion stats
  • 5 replies
  • 450 views
  • 0 likes
  • 4 in conversation