The SAS Output Delivery System and reporting techniques

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

Reply
Contributor
Posts: 67

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

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:

Result.png


Here is the result that I want:

result1.png


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

SAS Super FREQ
Posts: 8,740

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

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


right_justify_xp.png
Contributor
Posts: 67

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

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

SAS Super FREQ
Posts: 8,740

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

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.


decimal_1_xp.pngsansprinter_xp.pngsasweb_xp.pngmore_newline_xp.png
Contributor
Posts: 67

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

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.

Contributor
Posts: 61

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

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;

contiTable_count_percent.jpg

SAS Super FREQ
Posts: 8,740

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

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;

    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;


just_more_visible_with_table_lines.png
Ask a Question
Discussion stats
  • 6 replies
  • 3122 views
  • 3 likes
  • 3 in conversation