topic Re: How to right justify column name and value in ODS Excel output in ODS and Base Reporting

How to right justify column name and value in ODS Excel output

Belle — Thu, 27 Sep 2012 16:44:26 GMT

Hi Member,

I post this question in SAS Procedure, and I just realized this might be the right place to ask this question. Sorry for posting twice.

I am trying to produce a report by using ODS TAGSETS.EXCELXP.

How can I make all the column names and values right justify?

In my result, I used label name as output column name. Unfortunately, I did not get all the column names and values right justify.

I read in another post about how to adjust column, and I followed Cynthia suggestion in that post, but I still did not get what I want.

Here is the result that I got:

Here is the result that I want:

Here are the part of data from dataset FIN I use as input data:

Obs YYMM _NAME_ _LABEL_ MISSING NON_MISSING

1 200104 COUNT Frequency Count 18 53

2 200105 COUNT Frequency Count 67 .

Below are part of my program:

DATA FINAL (DROP=_NAME_ _LABEL_);

SET FIN;

IF MISSING = . THEN MISSING = 0;

IF NON_MISSING = . THEN NON_MISSING = 0;

PCT_MISS=MISSING/(MISSING+NON_MISSING);

FORMAT PCT_MISS PERCENT9.1 ;

LABEL justify=right MISSING = " Missing"

justify=right NON_MISSING = "Not Missing"

justify=right PCT_MISS = "PCT Missing";

RUN;

ODS TAGSETS.EXCELXP

OPTIONS(SHEET_NAME ="sheet name"

FROZEN_HEADERS='1'

ROW_REPEAT ='1'

WIDTH_FUDGE ='0.6'

ABSOLUTE_COLUMN_WIDTH='8,12.5,12.5,12.5'

AUTOFILTER ='1'

ORIENTATION ='LANDSCAPE'

FITTOPAGE ='YES'

);

proc print data = FINAL noobs label;

/*** here is the idea that i got from other post, but do not work on my case ***/

VAR MISSING / STYLE(DATA)={JUST=R}

STYLE(HEADER)={JUST=R};

VAR NON_MISSING / STYLE(DATA)={JUST=R}

STYLE(HEADER)={JUST=R};

VAR PCT_MISS / STYLE(DATA)={JUST=R}

STYLE(HEADER)={JUST=R};

run;

...

Thank you so much for the help in advance.

Belle

Re: How to right justify column name and value in ODS Excel output

Cynthia_sas — Thu, 27 Sep 2012 19:47:59 GMT

Hi:

All I can suggest is that you back up and start at a basic level. For example, look at the screenshot. The code that is in the screenshot created the right justified headers. All I did was put a "line feed" or "line break" ODS ESCAPECHAR instruction into each label to force a line break between label strings. Once I declared by ESCAPECHAR of ~, then ~n gave me the line feed. You did not say what version of SAS, so I'm using the original ESCAPECHAR syntax. (if ESCAPECHAR had been declared as '^', then ^n would be the line feed.)

It is still possible that some of your sub-options (such as column width) might be impacting the Excel columns and preventing you from seeing the impact of the right justify.

All of my LABEL strings are right justified. If this simple code doesn't work for you, then you should open a track with Tech Support for more detailed help. If this simple code does work for you, then start adding sub-options into the test code to try to find out whether the justification "breaks" with the introduction of one suboption or another. For most ExcelXP output, I don't move to absolute_column_width suboption until I need more control than I have with cellwidth.

cynthia

Re: How to right justify column name and value in ODS Excel output

Belle — Thu, 27 Sep 2012 21:12:45 GMT

Hi Cynthia,

Thanks for the help.

I followed your advise, and started from the simple options. I still encountered two problems:

(1) ESCAPECHAR did not work, I tried both '~' and '^'

(2) I formatted percentage to be one decimal, but the result shows two decimals.

Please advise.

Thanks

Re: How to right justify column name and value in ODS Excel output

Cynthia_sas — Thu, 27 Sep 2012 21:54:01 GMT

Hi:

Hmmmm, then it seems you have another issue. Whether I use ~n (as originally posted) or ~{newline 1} or ~{newline 2} as shown in the screenshots below, I do get a line feed in the headers when the XML file is opened in Excel. I would suggest that you make sure you have the most current tagset template and try again or open a track with Tech Support about this issue. My version of TAGSETS.EXCELXP is being run in SAS 9.3 and my output is opened in Office 2010. My version of TAGSETS.EXCELXP from my SAS log is: v1.122, 01/04/2011.

As for the percent issue. This is Microsoft default behavior. Basically, even if your SAS output is formatted the way you want, with leading zeroes, decimal places and percent signs, dollar signs, etc, Excel has defaults and uses them for HTML results and XML results (such as you create with ODS).

What you create with TAGSETS.EXCELXP is an Office 2003 Spreadsheet Markup Language XML file and so you must send a Microsoft format to ODS using the TAGATTR style attribute. I have a percent example in this paper:

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

So the bottom line on the column header and ESCAPECHAR is that I think you'll need to work with Tech Support. It looks to me like you have SAS 9.2? I no longer have 9.2 to test with -- but Tech Support could test code in earlier versions of SAS. The bottom line on the percent decimal places is that you need to use TAGATTR= in a style override.

cynthia

PS, there have been previous forum postings about putting the equivalent of ALT+ENTER into a cell when you use TAGSETS.EXCELXP, but I don't do that unless I really need to and I think you have other issues to resolve before you try something else.

Re: How to right justify column name and value in ODS Excel output

Belle — Thu, 27 Sep 2012 22:37:44 GMT

Thanks you so much, Cynthia.

Yes, I have SAS9.2. I am still working on right justify, luckily, I have fixed decimal issue.

Thanks again for all the suggestion.

Re: How to right justify column name and value in ODS Excel output

Chang — Wed, 09 Apr 2014 06:26:49 GMT

hi,

Chang here. I was wondering if the alignment has something to do with the format in the excel. I haven't had time to go thru the entire post. But I am able to right-align both column headers and values in my ODS RTF. My codes and a screenshot of the RTF:

ods rtf body="&tabFolder.\contiTable_count_percent.rtf"

style=journal

startpage=never

bodytitle;

proc print data= cTab_all

split=' '

noobs Style(HEADER) = {font_weight=bold};

var variable var_level;

var holiday_count_percent / STYLE(DATA)= {JUST=R}

STYLE(HEADER)= {JUST=R};

var weekday_count_percent / STYLE(DATA)= {JUST=R}

STYLE(HEADER)= {JUST=R};

title1 "Contingency table with counts and percent in parentheses";

title2 "Data folder=&tabFolder.";

title3 "Right-align column headers and values";

run;

ods rtf close;

Re: How to right justify column name and value in ODS Excel output

Cynthia_sas — Wed, 09 Apr 2014 15:59:11 GMT

Hi:

The justification is more visible if you use a style with interior table lines. Please run the attached code and compare the results. The right justification is more evident in the output with the wider cell widths and with table lines.

Cynthia

PS (By the way, it's better to start a new posting then to piggy back onto a 2 year old post.)

options topmargin=1in bottommargin=1in leftmargin=.1in rightmargin=.1in;
ods rtf body="c:\temp\contiTable_count_percent.rtf"
        style=rtf
        startpage=never
        bodytitle;

    proc print data= sashelp.class (obs=3)
               split=' '
               noobs Style(HEADER) = {font_weight=bold};
        var name age /style(header)={just=r cellwidth=1.5in}
                      style(data)={just=c cellwidth=1.5in};
        var sex height weight /STYLE(DATA)={JUST=R cellwidth=1.5in}
                               STYLE(HEADER)={JUST=R cellwidth=1.5in};
        title "Right-align column headers and right justify only some values";
title2 "and make the columns wide so the justification is evident";
    run;
ods rtf close;

options topmargin=1in bottommargin=1in leftmargin=.1in rightmargin=.1in;
ods rtf body="c:\temp\contiTablejournal_count_percent.rtf"
        style=journal
        startpage=never
        bodytitle;