07-23-2013 09:52 PM
I have a problem and I cannot figure out why this is happening. I have some code values (this cannot be changed, eg cannot prefix it) and each of those values needs to has the corresponding data exported to an individual tab.
I have the code to do this, it's not too tricky:
The above code has been simplified quite a lot, there's usually seven variables not just two. New codes will come up over the course of the year, so this is why think need the call execute statement (I would usually use a proc summary statement to get the unique values I'm interested). I've tried using proc export and even tagsets with no luck.
When I run this, most of the tabs are labelled as expected, but there are some which have an underscore as the prefix.
Actually, now that I've written this out, I think I see a pattern.
These are the codes which are one character less.
I cannot change these codes, they're fixed.
I've just tried:
but that doesn't really help, the output is still the same.
Right now, the solution is when the job has finished, someone manually opens the files and renames the tabs, ie removes the underscore.
Does anyone have a solution for this (which doesn't involve VB scripting).
We're running this on SAS Enterprise guide version:
5.1 (22.214.171.12469) Hot fix 7 (32-bit)
07-24-2013 11:19 AM
Try changing your macro to a proc export (instead of libname and using the sheet name). Not as clean unfortunately, but this errors seems to indicate its a known bug:
07-24-2013 12:20 PM
reeza, As I read the note, it is intended functionality avoiding the naming conventions with Excel usage. It is happening in proc export.
In that case it will always be problematic. (No bug/error). Maybe it is caused by an interface limitation and manually in Excel it is bypassed.
07-24-2013 12:46 PM
You could probably work around the issue of naming convention protection in SAS using DDE. It still involves some minor indirect excel VBA in that you need to know what excel commands allow you to rename a sheet but the SAS naming restrictions wouldn't apply as all would be done within excel.
somethin like this - all inserted within a different macro and use end=last set option with if last then call execute('%differentmacro()');
from g(where=(length(code)=5)); /* You might need to use PRX if you have some codes that are more complicated or more lengthy than 5 characters yet still break naming conventions */
options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office12\EXCEL.exe"';
z=sleep(5); /* 5seconds sleep, needs to be long enough for excel to open */
filename DDEcmds dde "excel|system";
%do i=1 %to &loopend;
put '[workbook.activate("_' "&&y&i" '")]';
* put '[activesheet.name="' "&&y&i"' "]'; /* this step is still bugging for some reason */
options xwait xsync;
*Note this is all untested, I don't use DDE nearly often enough to be confident about the syntax. However, this is an indication of the logic.
*edit* I've been trying to work it out with your sample g data and eventually broke it down into 2 macro to avoid having to clear the libname every step. However, I still can't figure out how to hit the right excel syntax. I've updated the post a bit
*edit* I don't know if this is up to date but 4570 - WORKBOOK.NAME command does not work with DDE to rename worksheets inExcel
If that has not been fixed in years, then I can't think of any alternative than using VBA