The SAS Output Delivery System and reporting techniques

ods excelxp : Left align , Right align two titles on the same line

Reply
Frequent Contributor
Frequent Contributor
Posts: 81

ods excelxp : Left align , Right align two titles on the same line

Hi Dear,

     Here is my simple coding  which create xml sheet.

ods tagsets.excelxp file="c:\temp.xls"
options(print_header='&L'
       embedded_titles='yes');

proc print data=sashelp.class noobs;
   title 'Header1 left'   ' Header2 right';
run;

ods tagsets.excelxp close;

I am currently using SAS 9.3 on  Windows.XP

I want the header1 to be appeared on the left and Header2 to be appeared on the right  but in the same line.  I am so tired of it make this successfull.

when I put j=l 'Header 1 left'   j=r ' Header2 right'; It brings the header1 on the left and the header2 to the right but header 2 appeared on the next line.  How can I make them header1 to left and header2 to right on the same line.

Thanks very much for your help in advance.

Regards.

Inpan

SAS Super FREQ
Posts: 8,868

Re: ods excelxp : Left align , Right align two titles on the same line

Hi:

  If you use 2 strings on the TITLE statement, Excel puts them on different lines - -HTML works differently in a browser -- the 2 strings are on the same line - -but both HTML and XML behave this way. That's just the way that Excel is. However, if you want your headers to be PRINT HEADERS, then you don't see those in the document normally. Look at this attached screen shot.

  The PRINT_HEADERS sub-option is how you pass headers to Excel and use Excel formatting strings like '&L' and '&R' (no need to use & because the simple & will work with these commands).

  The screen shot was produced with the program below.

cynthia

ods tagsets.excelxp file="c:\temp\headers.xml"

    style=sasweb

    options(print_header='&LHeader1 left &RHeader2 right'

            embedded_titles='yes' doc='Help');

  

proc print data=sashelp.class noobs;

   title j=l 'Embedded Title' ;

run;

  

ods tagsets.excelxp close;


xp_print_preview_headers.jpg
Frequent Contributor
Frequent Contributor
Posts: 81

Re: ods excelxp : Left align , Right align two titles on the same line

Posted in reply to Cynthia_sas

Thanks very much Cynthia,

So there is no way to do left and right align for two titles to be appeared in the same line. Because I have to create a program which can create an excel spread sheet with such display  not as dispaly as in the Print . In such case, all I have to create title only one line of long length title.

Thanks

Inpan

SAS Super FREQ
Posts: 8,868

Re: ods excelxp : Left align , Right align two titles on the same line

The only thing I can think of is to pad your TITLE statement with spaces in the middle. You'll probably have to test how many spaces are the right number, but trial and error are all I can think of. Something like this:

title 'Title Left                           Title Right';

Otherwise, I would suggest that you switch to PROC REPORT and use a spanning header on the left most and right most columns instead of a title.

proc report data=sashelp.class nowd;

    title 'Title Left                           Title Right';

    column ('Header Left' name) age sex height ('Header Right' weight);

run;

cynthia

Frequent Contributor
Frequent Contributor
Posts: 81

Re: ods excelxp : Left align , Right align two titles on the same line

Posted in reply to Cynthia_sas

Thanks very much Cynthia. This solution is great to me.

Thanks again.

Regards,

Inpan

Super Contributor
Posts: 358

Re: ods excelxp : Left align , Right align two titles on the same line

Hi:

I have had a similar problem and to resolve it I needed to make some minor changes to the ExcelXP tagset.  Doing this changed the header behaviour so that it was no longer a single, spanned cell in Excel.  Each cell of the output sheet could then be used to position the title. 

Using this template, I could specify the cell where I wanted each part of the title to appear - so part could begin in the left column, and then I could specify the second (or more) parts of the titles to start in cells further to the right.  It will not right-align the title, but you can use trial and error to get it in a column that you want.  Only difficulty is that you need to use XML tags to position the titles, but this is not too difficult.

I will post details if this is more what you are looking for.

Frequent Contributor
Frequent Contributor
Posts: 81

Re: ods excelxp : Left align , Right align two titles on the same line

Dear Sir / Madom,

     Yes please provide more details.

Thanks

Inpan

Super Contributor
Posts: 358

Re: ods excelxp : Left align , Right align two titles on the same line

Hi:

I will try to do my best to explain what we do....   Firstly, we are using tagset 186 as our production tagset and we are using 9.1 as our production release, although this has been testes on 9.3 successfully.

In the tagset, we have made the following changes starting at line 3349 of the code (the lines with the comments are the changes from the original code):

define event align_tag;
start:
break /if $align_tag;
put '<Alignment';
do /if cmp($cell_class, 'systemtitle');                   /* DD */
put ' ss:Wraptext="0"';                               /* DD */
else /if cmp($cell_class, 'data');                        /* DD */
put ' ss:WrapText="0"';                               /* DD */
else;                                                     /* DD */
put ' ss:WrapText="1"';                               /* DD */
done;                                                     /* DD */
set $align_tag "True";
finish:
break /if ^$align_tag;
putl '/>';
unset $align_tag;
end;

Then, in the code that generates the report titles, you code something like:

  title1 'Left Portion Of Title'

         '</Data></Cell><Cell ssSmiley FrustratedtyleID="systemtitle" ss:Index="6"><Data ss:Type="String">'

         'Cenetered Portion Of Title'             

         '</Data></Cell><Cell ssSmiley FrustratedtyleID="systemtitle" ss:Index="14"><Data ss:Type="String">'

         'Right Portion Of Title';

(Just cut and past this as the title - that's how it got here).

The left portion would start in relative cell #1, the centered portion starts in cell #6 and the right portion starts in cell #14.

The positions of the cells on in the resulting spreadsheet would depend in the absolute widths of the cells if specified in the ExcelXP options.

Let me know if you have any problems with this.  It has worked for us for may years.

Ask a Question
Discussion stats
  • 7 replies
  • 3692 views
  • 1 like
  • 3 in conversation