09-27-2013 06:37 PM
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.
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"
sheet="1.A - Foreclosure";
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!
09-28-2013 03:05 PM
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.
09-28-2013 04:15 PM
If you read the doc on PROC EXPORT, it clearly explains how SHEET= works:
--- begin quote ---
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'";
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).
09-28-2013 04:59 PM
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.
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!!!
09-28-2013 07:41 PM
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.
09-28-2013 05:49 PM
Yes, of course you can do it with DDE. Take a look at the various examples at:
09-28-2013 06:14 PM
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.