Blank rows in Excel between Titles

[crkraft]

I am using the tagset MSOffice2K and am having trouble with the output in Excel putting a blank row between titles. For example, I have

title1 'The University of Alabama';
title2 'C&BA Course Sections';
title3 'Fall 2008';

and want the titles to be in the first three rows of the Excel sheet. However, a blank line is inserted between title1 and title 2 and another blank line is inserted between title2 and title3. How can I stop this?

Thanks in advance!!

 

[ChrisW75]

Hi crkraft.
I haven't used this particular tagset, however, have you tried the "EXCELXP" tagset? t specifically works on Excel, and doesn't insert blank lines.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[crkraft]

Thanks for the info! I did figure it out eventually. Here is the code to write titles directly one after another in Excel, without blank lines in between:

ods tagsets.msoffice2k file="test.xls"
headtext='<style>
h1 {margin:0;}
table {margin-top: 1em; margin-bottom: 0em;}
</style>';

The "h1 {margin:0;}" removes the blank lines between the titles, and the "table {margin-top: 1em; margin-bottom: 0em;}" puts a blank line between the titles and the body.

 

[crkraft]

Chris,

I decided to check out the excelxp tagset, and really like the options on it including printing to multiple tabs, being able to include formatting options within proc tabulate and proc report, controlling excel's page set-up within sas, etc. Of course, when we get what we want, we want more! I have been using proc tabulate and the by group statement to write several tables to excel. I know how to write the value of the variable in the title in each sheet. However, I can't figure out how to write the value of the variable as the sheet name. Do you know if this is possible?

Here is an example of my code:

option missing = '' nobyline;
ods tagsets.excelxp file="test.xls"
style = uaOIRA
options(embedded_titles='yes'
sheet_name='Retention'
suppress_bylines = 'yes');

proc tabulate data=new_groups;
format grp $grp.;
class grp ok1st;
var act hc adhc excl ok2nd ok3rd/ style=[just=c];
table ok1st='',
act*mean=''*[style=[tagattr='format:#,##0.0']]
(hc excl adhc)*sum=''
(ok2nd ok3rd)*(sum='No.'* pctsum<adhc>='%')
/ box=[label='Fall' style=[vjust=t just=c ]];
by grp;
title3 'Retention and Graduation Data for #byval(grp) Majors';
run;

ods tagsets.excelxp close;

The tab names come up as "Retention", "Retention 2", ...

I would love to be able to put the value of the groups on the tabs so that it is easy to get to the needed data. Any suggestions? Thanks so much! You are awesome on this forum! I've used lots of your posts!

Thanks in advance!
Christine

PS - I just read your tech tips on problem solving, and I see that I should have posted my answer to my previous question when I found it so that other people wouldn't waste their time. I am totally on that now! Maybe I will figure this one out.

 

[ChrisW75]

Hi Christine, thanks for the vote of confidence!
As far as I'm aware, there's not a way of feeding the variable back to the tagset in order for it to be used in the sheet name. The way I've handled this in the past is to do one step per sheet and put a macro variable into the sheetname.
If you use the ODS tagsets.excelxp options(....); statement, without using the FILE= option you can specify a different sheetname (plus other options) for each sheet.

Another option would be to create an Excel macro to post-process the worksheets and pick the name up from the specific location in the title, then rename the worksheet accordingly...

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[alunbrain]

Hi
you could try using DDE commands
I have yet to test this, but should be ok. Not sure how familiar you are with DDE commands though

would also need a bit more work to output your data set to the sheet.
cheers


data work.title1;
format var1 var2 var3 $60.;
var1= 'The University of Alabama';
'The University of Alabama';
var2 'C&BA Course Sections';
var3 'Fall 2008';

run;

options noxwait noxsync mprint;
filename cmds dde 'excel|system';

filename commands dde 'excel|system';
options noxwait noxsync;

x '"c:\program files\microsoft office\office\excel.exe"
"d:\temp\book1.xls"';


filename sumryX dde 'excel|[book1.xls]Sheet1!r1c1:r1c1' notab lrecl=20000;

data sumry;
set work.title1;
file sumryX;
put var1 ;
run;

filename sumryX dde 'excel|[book1.xls]Sheet1!r2c1:r2c1' notab lrecl=20000;

data sumry;
set work.title1;
file sumryX;
put var2 ;
run;


filename sumryX dde 'excel|[book1.xls]Sheet1!r3c1:r3c1' notab lrecl=20000;

data sumry;
set work.title1;
file sumryX;
put var3 ;
run;