10-17-2014 06:58 AM
Dear Cynthia / All
I am sharing one link of sas support as well as screen shot where u can check we can rename the sheet name using ods TAGSETS.MSOFFCE2K_X but still I am facing one problem. when I follow this code and generate my excel sheet using ods tagsets.MSOFFICE2K_X and once the file has been generated and I go to open this file it is giving a one pop message and due to this I am unable to check my output. I am sharing screen shot this pop message , please check and help me out why this is happening because I am stuck here.
10-20-2014 10:30 PM
I am teaching this week and not able to watch the threads as closely. It looks like you got some responses.
My only comment is to take SAS out of the equation: does Excel allow you to even have a "null" sheet name in Excel? I thought you had to have something as the sheet name. What does a "null" sheet name mean to you? When I try to leave a sheet name "blank" or "null", Excel gives me an error. How do you get around this error when SAS is not in the picture? SAS has to play by Excel's rules. My recommendation is that you open a track with Tech Support and define for them what a "null" sheet name is and then ask them if it is even possible.
10-21-2014 05:10 AM
I applied a same code using ODS TAGSETS.MSOFFICE2K_X which is mentioned in sas support link but still sheet name is not changing. I am not understanding if this code is mentioned on sas support link then why it doest not work and unable to renaming excel report sheet name
(Actually I have more then 50 reports where I have to rename excel sheet name by sas code only because by default it is not taking more then 32 character name and this is mandatory for me to use ODS TAGSETS.MSOFFICE2K_X only..please if possible then help me or guide me how can I do it.
10-20-2014 02:15 AM
You should follow any of the options mentioned in:
For example copy the code msoffice2k_x.sas and run it firstly, before run the example you mentioned. After that run the example code and you will be able to open the output directly.
10-20-2014 03:08 AM
This additional code msoffice2k_x has been run successfully but till as per code sheet name is not renaming.Still this sas code is not giving me desired output.
10-20-2014 03:57 AM
Please be specific, as i understand you have tow problem mentioned:
1. The pop message
-->If you run the msoffice2k_x.sas succesfully when starting your SAS session, then creating your EXCEL sheet. You will not face the pop message that bothering you.
2. The rename of the sheet other than the default in MS Excel
-->You can rename the sheet by setting the sheet_name options as you want, but you can not set it to null "" because MS Excel not allowing that and if you did so it will be set to the default by MS Excel when you open it as "sheet1".
So for now, kindly do the following :
3.1. Open new SAS session.
3.2. Run the msoffice2k_x.sas
3.3. Run the following example code:
ods tagsets.msoffice2k_x file="c:\temp\worksheetoption.xls" style=normal
proc print data=sashelp.prdsale(obs=50);
ods tagsets.msoffice2k_x close;
3.4. after that open the excel and you will find the sheet name as "Ameey Sheet".
Please reply by what you find.
10-20-2014 04:38 AM
Yes, we were talking about this at this thread: https://communities.sas.com/thread/62123
Null sheet names are not recommended. The msoffice2k tagset is rather old, I would still recommend excelxp tagset as its newer and has far more options (plus its better supported in Excel), and you can still arrive at the same output as from the other tagset (unless of course you really want HTML output).