Export data to Excel file - Multiple tabs with special names

Reply
Super Contributor
Posts: 418

Export data to Excel file - Multiple tabs with special names

Hello everyone. I am trying to take a series of SAS datasets and export them to an excel file where each dataset needs to be represented on its own excel tab.

The issue I am having is that I need the excel tabs to start with special characters, and or have spaces represented within them.


Example:

Sas dataset                              Excel tab name

Foreclosure                                   1.A - Foreclosure.

So in this case, I would like to do something like the following...

proc export data=foreclosure outfile="c:\data\mydatfile.xlsx"

replace;

sheet="1.A - Foreclosure";

run;

However when I do this, the tab name gets converted to _1_A___Foreclosure. This is not acceptable given my circumstances, but I am not sure of the best way (or any way for that manner) to get this to work!

Thanks for your help in advance!

Brandon


Respected Advisor
Posts: 3,899

Re: Export data to Excel file - Multiple tabs with special names

If you want to use literals then the syntax is ''n

I believe the only thing you need to do is:

sheet="1.A - Foreclosure"n;

PROC Star
Posts: 7,364

Re: Export data to Excel file - Multiple tabs with special names

  That is what I would have thought, but it didn't work fore me

Super Contributor
Posts: 418

Re: Export data to Excel file - Multiple tabs with special names

Oh i'm sorry I should have specified I already tried name literals with Validvarname  and a couple of other options. It just doesn't seem to be working.

Is there another way besides proc export that could do this? I'm a bit surprised that SAS can't export to excel when the tab name starts wtih special characters.

SAS Super FREQ
Posts: 8,743

Re: Export data to Excel file - Multiple tabs with special names

Hi:

  If you read the doc on PROC EXPORT, it clearly explains how SHEET= works:

--- begin quote ---

SHEET= sheet-name
identifies a particular worksheet in an Excel workbook. Use the SHEET= option only when you want to import an entire worksheet. The sheet-name can contain up to 31 characters. If the EXPORT procedure sheet-name contains special characters (such as space) SAS converts it to an underscore.

The following examples demonstrate how SAS converts non-compliant sheet names.

• The space is converted to an underscore. Employee Information becomes Employee_Information
• If the sheet name contains single quotes, keep the single quotes as part of the sheet name in order to be able to access the sheet. SHEET="'My#Test'";

--- end quote ---
from:
http://support.sas.com/documentation/cdl/en/acpcref/64792/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

Further, this note http://support.sas.com/kb/14/475.html  explains that ever since SAS 8, a sheet name that looks like a cell reference gets a leading underscore. You might want to check with Tech Support, but it is possible that your 1.A is looking like a cell reference.

Also, check the rules for sheet names in an Excel worksheet...I couldn't find the rules on the Microsoft site, but this old posting seems to indicate some Excel behavior (not related to SAS) such that they recommend only alphanumeric characters. ( Microsoft Excel - While renaming a sheet or chart, you entered ... - FaultWire Forum).

cynthia

Super Contributor
Posts: 418

Re: Export data to Excel file - Multiple tabs with special names

Hi cynthia. Thanks for your time and response. The issue I have is that I know Sas cannot do this with proc exoprt, but I waas wondering if it could be done with perhaps A dde exchange etc..?

I have been tasked to do this, so having it not work is not an option. I basically need to populate a sheet (a new excel file works as well) that has excel tab names starting with numbers.

The tab names are as follows.

1.A

2.B

3.C.ii.v

etc..

So bacially, I am not sure if this is something sas can do using ANY technique. I know proc export doesn't work, but I also know there are lots of other methods in SAS. That is why I am here, to see if there is another method!!!

Brandon

Respected Advisor
Posts: 3,899

Re: Export data to Excel file - Multiple tabs with special names

Reading the docu! Why haven't you told us this earlier? :smileysilly:

Looks to me like a behaviour of Proc Import which would profit from change as it is not really intuitive and differs from how SAS syntax works in other areas.

PROC Star
Posts: 7,364

Re: Export data to Excel file - Multiple tabs with special names

Yes, of course you can do it with DDE.  Take a look at the various examples at:

http://www.sas-consultant.com/professional/how-code-SUGI27.zip

Super User
Posts: 17,899

Re: Export data to Excel file - Multiple tabs with special names

Does ODS Tagsets.excelXP generate the same issue? I've seen those generate tab names that have special characters. 

There's macros to convert the XML to XLSX afterwards.

Ask a Question
Discussion stats
  • 8 replies
  • 1280 views
  • 0 likes
  • 5 in conversation