DATA Step, Macro, Functions and more

Export to Excel has underscore prefixing some sheets

Reply
Frequent Contributor
Posts: 75

Export to Excel has underscore prefixing some sheets

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:

data g; 
  
input code $6. val; 
  
datalines
Q0603a 1 
N0706a 1 
V0706a 1 
N0710a 1 
N0710b 1 
Q0710a 2 
V0712a 3 
N0712a 4 
V0712b 1 
Q0802a 3 
V0804a 4 
W0807  5 
Q0811a 1 
Q0811b 2 
V0902a 1 
N0903a 1 
Q0905  1 
N0911a 1 
V1003a 1 
W1003a 1 
V0910a 1 
Q1012  1 
Q1101  1 
V1101  4 
Q1102  6 
W1102  1 
V1102  2 
W1111  1 
V1112  1 
Q1202  2 
N1202  3 
T1301  1 
N1301a 1 
N1301b 1 
Q1301  1 

run
libname x pcfiles path = "C:\test.xls"
%macro exp(c)
data
x.&c.; set g; 
   where code eq
"&c."
run; 
%mend
data _null_
  
set g; 
  
call execute(cats('%exp(', compress(code, '', 'kad'), ')') ); 
run
libname x clear

^^
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:

%macro exp(c);
%let c2 = %cmpres(&c.);
data x.&c2.; set g;  
   where code eq
"&c.";
run;
%mend;

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.

Super User
Posts: 19,878

Re: Export to Excel has underscore prefixing some sheets

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

Trusted Advisor
Posts: 3,215

Re: Export to Excel has underscore prefixing some sheets

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.  

---->-- ja karman --<-----
Super Contributor
Posts: 339

Re: Export to Excel has underscore prefixing some sheets

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

Ask a Question
Discussion stats
  • 3 replies
  • 1326 views
  • 0 likes
  • 4 in conversation