Proc Report--different formatting for row

[A_Swoosh]

I have a table that is current laid out in the way I want. The only issue is that when I went to assign the format, it carried over the format for all values. I have a row that should be total, but I'm unsure how to strip the formatting on this row only in proc report. Can I do a call define option or proc format if value is less <100, use a percentage format?

%let gray=CXBFBFBF;
    %let blue=CX13478C;
    %let purple=CXDEDDED;
    title j=c h=10pt f='Calibri' color=black "Table 1-Distribution, CY2016-CY2018";
        options orientation = landscape nonumber nodate leftmargin=0.05in rightmargin=0.05in;
        ods noproctitle noresults escapechar='^';
        ods rtf  file = "path.rtf";
         proc report data= work.temp nowd spanrows  style(report)={width=100%}
            style(header)=[vjust=b font_face = Calibri fontsize=9pt font_weight=bold background=&blue. foreground=white borderrightcolor=black];
            /*List variables in order to select order of columns in table*/
        col ( m_type 
                  ('^S={borderbottomcolor=&blue. vjust=b borderbottomwidth=0.02 }'('^S={borderbottomcolor=&blue. vjust=b borderbottomwidth=0.01 cellheight=0.20in}Age in Years' d_char_desc)) 
                  ('^S={cellheight=0.20in}Missing Information' 
                  ('^S={borderbottomcolor=&blue. borderbottomwidth=0.02 cellheight=0.18in}' percentage16_1)
                  ('^S={borderbottomcolor=&blue. borderbottomwidth=0.02 cellheight=0.18in}' percentage17_1)
                  ('^S={borderbottomcolor=&blue. borderbottomwidth=0.02 cellheight=0.18in}' percentage18_1))
);
define m_type /order=data group noprint style = [vjust=b just=left cellwidth=0.60in font_face='Times New Roman' fontsize=9pt];

        define d_char_desc / order=data display  style = [vjust=b just=left cellwidth=0.60in font_face='Times New Roman' fontsize=9pt]
                         '' style(header)=[vjust=b just=left cellheight=0.18in] style(column)=[vjust=b just=left cellheight=0.35in cellwidth=0.60in];
        define percentage16_1  /display style = [vjust=b just=center  cellwidth=0.60in cellheight=0.05in font_face='Times New Roman' fontsize=9pt] 
                         'CY2016' style(header)=[vjust=b just=center cellheight=0.18in] style(column)=[vjust=b just=center cellheight=0.20in cellwidth=0.40in];
        define percentage17_1 /display style = [vjust=b just=center  cellwidth=0.45in cellheight=0.05in font_face='Times New Roman' fontsize=9pt] 
                         'CY2017' style(header)=[vjust=b just=center cellheight=0.18in] style(column)=[vjust=b just=center cellheight=0.20in cellwidth=0.40in];
        define percentage18_1  /display style = [vjust=b just=center  cellwidth=0.45in cellheight=0.05in font_face='Times New Roman' fontsize=9pt] 
                         'CY2018' style(header)=[vjust=b just=center cellheight=0.18in] style(column)=[vjust=b just=center cellheight=0.20in cellwidth=0.40in];
compute m_type;    
if m_type = 'm_tot' then
call define (_row_, 'style', 'style=[fontweight=bold background=&gray. font_face=Times]');
endcomp;
run;
ods rtf close;

Want: total line to show no decimals as they are count but the rest of the table to keep same format.Have

[ballardw]

I think that you may want to provide some example data so we can mess with your code.

One suspects that you may not have the correct data structure and perhaps you are looking at a COMPUTE Before or Break of some sort.

[A_Swoosh]

data temp;
infile DATALINES;
input m_type $ d_char_desc $ percentage16_1 percentage17_1 percentage18_1;
CARDS;
m_tot total(n) 784 957 784
m_age ^{unicode2264}15 30.24554548 30.91548487498 11.29888451
m_age 16-17 31.398946456 32.832646884 21.43564648
m_age 18-20 16.66679995 14.8999945645 44.44444444
;
run;

This is non-formatted code. It's already in percent as I calculated from a proc frequency statement.

I used this format to get it in the format seen above:

proc format;
picture mypct (round) low-high='00,009.9%';
run;

[ballardw · Accepted solution]

I suspect that you may not have done yourself any favors by not using actual decimal percentages because you are going to be hosed for any "count" that totals less than 100.

If your n's are always greater than 100 you might try

proc format;
picture mypct (round) low-100='00,009.9%'
100 <-high = '000,009'
;
run;

Another option might be to go back to your raw data and use a different procedure like tabulate that allows some slightly different rules for where statistics cross:

proc format library=work;
   value myage 
   low -13 = '<13'
   13 -14  = '13-14'
   15-high = '15+';
run;
proc tabulate data=sashelp.class;
   class age sex;
   format age myage.;
   table All='Total (n)'*n=' '  age=' '*colpctn=' ',
         Sex
         /row=float
   ;
run;

If your orginal data was something like age and year then this is a good example as SEX would go where your year is to create columns. You should have the SASHELP.CLASS data set available.

Note the use of a format to create groups and assign text for display.

The ways to apply styles very a bit with Tabulate but can be applied. You may want the BOX='text' option to place text in the upper left.

[A_Swoosh]

In all cases, my data is >100 for high case so I just elected to use that but I see your point about the tabulate particularly in the case where count <100. 

[Cynthia_sas]

Hi:
Just as you can use 'style' as the second argument to call define, there is also a 'format' argument allowed. For an example of using the 'format' argument, please see pages 8/9 in this paper: http://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf .

Cynthia

[A_Swoosh]

Following the examples on page 8-9 does not produce any formatting when specifying 'format' or 'style'.

E.g. 

compute m_type;    
if m_type = 'm_tot' then do;
call define (_row_, 'style', 'style=[fontweight=bold background=&gray. font_face=Times]');
call define(_col_,'style','style={tagattr="format:###,##0"}');
/*OR*/
call define (_col_,'format','comma6.');
end;
else if m_type ne 'm_tot' then do;
call define (_col_,'style', 'style={tagattr="format:00,009.9%"}');
end;
endcomp;

[Cynthia_sas]

Hmm...are you using ODS RTF or ODS EXCEL??? TAGATTR does NOT work for ODS RTF -- it is an Excel-specific method for changing format. And, you didn't use quite the syntax. It looks to me as through m_type is a character string. It won't take a comma6. numeric format. What is the particular column you want to change??? It looks to me like you want to change the format of the "percentage" variables: percentage16_1, percentage17_1 and percentage18_1. Here's an example of changing a format based on a character value -- in 3 different places:

In the above code, AGE, HEIGHT and WEIGHT were all defined as DISPLAY usage -- in which case, I can refer to them by their "simple" names. If they had a usage of ANALYSIS/SUM, then I'd have to use AGE.SUM, HEIGHT.SUM or WEIGHT.SUM in the CALL DEFINE.

 
Hope this helps clarify.
Cynthia

[A_Swoosh]

ODS RTF. 

1. m_type is a character string.
2. I want to change all columns (e.g. percentage variables) if the m_type character ='m_tot' (i.e. total)
   • This would be the entire first row.
3. Similarly, I want to change the columns if m_type does not equal 'm_tot' to use a customized format (00,009.9%) since they were already pre-aggregated using a proc freq statement. 

[Cynthia_sas]

Hi:
Well, as I explained, you cannot use TAGATTR with ODS RTF. If you need a custom percent format, then I recommend a PICTURE format for percent. Typically folks use a picture format with PROC TABULATE, for percents, which is where you find an example, here: http://support.sas.com/kb/38/001.html. So for most of the rows, you would just define the MYPCT custom format and use it on the DEFINE statement for each of your percent variables.

Then you'd need to use the CALL DEFINE method, with the 'format' argument on the row you want to change.

The trick is that PROC REPORT has a "left to right" rule. So, in my example, I can't "touch" age, height or weight in the COMPUTE block for NAME. But in the COMPUTE block for WEIGHT, I can touch all 3 of those report items.

So just as I cannot change AGE on the COMPUTE block for NAME, you cannot change your percent variables on the COMPUTE block for m_type -- because those variables appear AFTER m_type on the COLUMN statement.

PROC REPORT fills the report row one row at a time, one cell at a time, working from left to right on the row. So conceptually, in my example, at the point in time when PROC REPORT has placed NAME on the report row, I can change the style characteristics for the whole ROW, but I cannot change the individual numeric formats for the AGE, HEIGHT and WEIGHT variables in the COMPUTE block for NAME. When PROC REPORT is putting NAME on the row, it has NO visibility of the AGE, HEIGHT and WEIGHT variables. The background color change you make in the COMPUTE block for NAME is a "general" one, it can be applied to the whole row, no matter what cells are going to be placed on the row.

But the formats are specific. The formats, in my example, for AGE, HEIGHT and WEIGHT can be applied conditionally based on NAME, as I show in my example, but NOT in the COMPUTE block for NAME.

Hope this clarifies,
Cynthia