Hi,
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.
_W0807
_Q0905
_Q1012
_Q1101
_V1101
_Q1102
_W1102
_V1102
_W1111
_V1112
_Q1202
_N1202
_T1301
_Q1301
^^
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 (5.100.0.12269) Hot fix 7 (32-bit)
Thanks.
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:
14475 - PROC EXPORT to Excel inserts leading underscore in Worksheet name
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.
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()');
proc sql;
select code
into :y1-:y99999
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 */
run;
%let loopend=&sqlobs;
options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office12\EXCEL.exe"';
data _null_;
z=sleep(5); /* 5seconds sleep, needs to be long enough for excel to open */
run;
filename DDEcmds dde "excel|system";
data _null_;
file ddecmds;
put '[open("C:\test.xls")]';
%do i=1 %to &loopend;
put '[workbook.activate("_' "&&y&i" '")]';
* put '[activesheet.name="' "&&y&i"' "]'; /* this step is still bugging for some reason */
%end;
put '[save()]';
put '[error("false")]'
put '[quit()]';
run;
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.
Vincent
*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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.