Format a PROC REPORT table: multiple formatting rules based on defined numeric type

[Jack1]

I have the following data:

I need to create a summary report from the data above (where prod='fast' and type='East' with the following formatting:

* Revenue (level values) to 1 decimal place

* ADV (level values)  to zero decimal places

* Yield (level values) to 2 decimal place

* Revenue, ADV and Yield (YoY %) to 1 decimal place, red and in brackets if negative

* Column 'FY23' shaded in gold 

When I run my PROC report code, this is what I am getting:

The formatting is not working per my SAS code:

proc format;
value FY20p low -< 0 = 'red';
value FY21p low -< 0 = 'red' ;
value FY22p low -< 0 = 'red';
value FY23p low -< 0 = 'red' ;

ods region;
ods text='Annualized Summary: History plus Forecast';
proc report data=annual_view_pct1 nowd spanrows center;
where prod='fast' and type='West';
column calcs metrics1 FY20 FY21 FY22 FY23;
define calcs / "Calculation" group  ;
define metrics1 / "Metric" display ;
define FY20 / analysis style(column)=[foreground=FY20p.];
define FY21 / analysis style(column)=[foreground=FY21p.] ;
define FY22 / analysis style(column)=[foreground=FY22p.];
define FY23 / analysis style(column)=[foreground=FY23p.];

compute FY20;
   if metrics1='Revenue $M' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.1');
   end;
   else if metrics1='ADV' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.0');
   end;
   else if metrics1='Yield' and calcs='Levels:' then do;
      call define(_col_,'format','comma9.2');
   end;
endcomp;

compute FY21;
   if metrics1='Revenue $M' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.1');
   end;
   else if metrics1='ADV' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.0');
   end;
   else if metrics1='Yield' and calcs='Levels:' then do;
      call define(_col_,'format','comma9.2');
   end;
   else if metrics1 ='Revenue $M' and calcs='YoY %:' then do;
      call define(_col_,'format','negparen7.1');
   end;
   else if metrics1 ='ADV' and calcs='YoY %:' then do;
      call define(_col_,'format','negparen7.1');
   end;
   else if metrics1 ='Yield' and calcs='YoY %:' then do;
      call define(_col_,'format','negparen7.1');
   end;
endcomp;

compute FY22;
   if metrics1='Revenue $M' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.1');
   end;
   else if metrics1='ADV' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.0');
   end;
   else if metrics1='Yield' and calcs='Levels:' then do;
      call define(_col_,'format','comma9.2');
   end;
   else if metric1 ='Revenue $M' and calcs='YoY %:' then do ;
      call define(_col_,'format','negparen7.1');
   end;
   else if metric1 ='ADV' and calcs='YoY %:' then do ;
      call define(_col_,'format','negparen7.1');
   end;
   else if metric1 ='Yield' and calcs='YoY %:' then do ;
      call define(_col_,'format','negparen7.1');
   end;
endcomp;

compute FY23;
   if metrics1='Revenue $M' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.1');
   end;
   else if metrics1='ADV' and calcs='Levels:' then do;
       call define(_col_,'format','comma9.0');
   end;
   else if metrics1='Yield' and calcs='Levels:' then do;
      call define(_col_,'format','comma9.2');
   end;
   else if metrics1 in('Revenue $M', 'ADV','Yield') and calcs='YoY %:' then do;
      call define(_col_,'format','negparen7.1');
   end;
   call define(_col_,'style','style={background=gold}');
endcomp;
run;
title1;

What am I doing wrong?  Is there a simpler way to apply my multiple formatting rules?

Jack

[Cynthia_sas]

Hi:
Unfortunately, since you posted your data as a screen shot, no one can run your code against your data without re-creating the data file. Also, you didn't show ALL your code. What is your destination of interest? ODS HTML, RTF, PDF??
Off the top of my head, I would say that you are having issues with the value for your CALCS variable on the report. If you look carefully at the report screen shot, you'll see that CALCS is a GROUP item. That means the value of CALCS will ONLY appear on the first report row for the group. The value of CALCS --on the report row-- is blank on the rows for ADV and YIELD. So that condition where CALCS='Levels" and METRICS1='ADV' is never true. Although there is a value for CALCS on every row in the DATA, there is NOT a value for CALCS on every row on the summarized report. You could work around this by creating another variable that you can test, but NOPRINT the variable so it doesn't show on the report. Or you could use a temporary variable, but either way, without data to work with, no one can tweak your code to make a concrete suggestion. There have been examples of both NOPRINT and temporary variables in PROC REPORT previously posted in the Forum, so perhaps searching on temporary variables or helper variables and PROC REPORT may help you uncover some of the previous posts.
Cynthia

[Jack1]

Thanks for the reply...here is the data and the code to create the table:

DATA trial;
length prod $4 calcs type metrics1 $11 FY20 8 FY21 8 FY22 8 FY23 8 FY24 8;
infile cards dsd dlm='|' truncover ;
INPUT prod calcs type metrics1 FY20 FY21 FY22 FY23 FY24;
CARDS;
fast|Levels:|West|Revenue $M|35.459918|57.877013|63.33193078|63.47928713|63.91890371
fast|Levels:|West|ADV|6754.808|11008.52988|11459.9335|11105.33631|10766.00013
fast|Levels:|West|Yield|20.99832771|20.94609468|22.10551425|22.86442676|23.65381607
fast|YoY %:|West|Revenue $M|.|63.21812419|9.425016073|0.232673083|0.692535469
fast|YoY %:|West|ADV|.|62.97324632|4.100489522|-3.094234271|-3.055613651
fast|YoY %:|West|Yield|.|-0.248748522|5.535254101|3.433136645|3.45247803
fast|Levels:|East|Revenue $M|161.1857336|144.475665|146.7144394|152.5800064|159.3485751
fast|Levels:|East|ADV|29442.064|24978.33865|24417.55367|24560.60492|24779.70283
fast|Levels:|East|Yield|21.89870025|23.04397691|24.03425688|24.84955185|25.61995539
fast|YoY %:|East|Revenue $M|.|-10.366965|1.54958578|3.997948032|4.436078429
fast|YoY %:|East|ADV|.|-15.16104766|-2.245085151|0.585854137|0.892070469
fast|YoY %:|East|Yield|.|5.229884164|4.297348405|3.392220431|3.100271361
fast|Levels:|Total|Revenue $M|196.6456516|202.352678|210.0463701|216.0592935|223.2674789
fast|Levels:|Total|ADV|36196.872|35986.86853|35877.48717|35665.94123|35545.70296
fast|Levels:|Total|Yield|21.730679|22.40222618|23.41818078|24.23144166|25.02445567
fast|YoY %:|Total|Revenue $M|.|2.902187956|3.802120246|2.86266475|3.336207025
fast|YoY %:|Total|ADV|.|-0.580170226|-0.303947973|-0.589634219|-0.337123498
fast|YoY %:|Total|Yield|.|3.090318413|4.535060926|3.472775642|3.272665411
;
RUN;

proc format;
value FY20p low -< 0 = 'red';
value FY21p low -< 0 = 'red' ;
value FY22p low -< 0 = 'red';
value FY23p low -< 0 = 'red' ;
run;

proc report data=trial nowd spanrows center;
where prod='fast' and type='West';
column calcs metrics1 FY20 FY21 FY22 FY23;
define calcs / " " group ;
define metrics1 / "Metric:" display ;
define FY20 / analysis 'FY20' format=negparen7.1 style(column)=[foreground=FY20p.];
define FY21 / analysis 'FY21' format=negparen7.1 style(column)=[foreground=FY21p.];
define FY22 / analysis 'FY22' format=negparen7.1 style(column)=[foreground=FY22p.];
define FY23 / analysis 'FY23' format=negparen7.1 style(column)=[foreground=FY23p.];
run;

So here is what I am getting:

And this is what I am looking for:

* If Levels:

   - Revenue to 1 decimal with comma separator, ADV to zero decimals (with comma separator), Yield to 2 decimals 

* If YoY %:

   - All metrics to 1 decimal, color red and in brackets if negative

Desired table:

Maybe there is a better way to code this?

Jack

[PaigeMiller · Accepted solution]

Here is my solution. I have only applied it to column FY23, you can do the extra work to make it apply to the other columns.

proc report data=trial nowd spanrows center;
    where prod='fast' and type='West';
    column calcs metrics1 FY20 FY21 FY22 FY23;
    define calcs / " " group ;
    define metrics1 / "Metric:" display ;
    define FY20 / analysis 'FY20' format=negparen7.1 style(column)=[foreground=FY20p.];
    define FY21 / analysis 'FY21' format=negparen7.1 style(column)=[foreground=FY21p.];
    define FY22 / analysis 'FY22' format=negparen7.1 style(column)=[foreground=FY22p.];
    define FY23 / analysis 'FY23' format=negparen7.1 style(column)=[foreground=FY23p.];
    compute before calcs;
        _c=calcs;
    endcompute;
    compute fy23;
        if metrics1=:'Rev' and _c='Levels:' then call define(_col_,'format','8.1');
        if metrics1='ADV' and _c='Levels:' then call define(_col_,'format','comma10.0');
        if metrics1='Yield' and _c='Levels:' then call define(_col_,'format','8.2');
        if _c=:'YOY' then call define(_col_,'format','10.1');
    endcompute;
run;

[Jack1]

This works great, thanks for the reply!

Jack

[Jack1]

One modification....I need to add a section showing YoY change section in my table, but when I add the code to format, the formatting is not coming out correctly.

Here is a modified version of the data above:

DATA trial;
length prod $4 calcs type metrics1 $11 FY20 8 FY21 8 FY22 8 FY23 8 FY24 8;
infile cards dsd dlm='|' truncover ;
 INPUT prod calcs type metrics1 FY20 FY21 FY22 FY23 FY24;
CARDS;
fast|Levels:|West|Revenue $M|35.459918|57.877013|63.33193078|63.47928713|63.91890371
fast|Levels:|West|ADV|6754.808|11008.52988|11459.9335|11105.33631|10766.00013
fast|Levels:|West|Yield|20.99832771|20.94609468|22.10551425|22.86442676|23.65381607
fast|YoY %:|West|Revenue $M|.|63.21812419|9.425016073|0.232673083|0.692535469
fast|YoY %:|West|ADV|.|62.97324632|4.100489522|-3.094234271|-3.055613651
fast|YoY %:|West|Yield|.|-0.248748522|5.535254101|3.433136645|3.45247803
fast|Levels:|East|Revenue $M|161.1857336|144.475665|146.7144394|152.5800064|159.3485751
fast|Levels:|East|ADV|29442.064|24978.33865|24417.55367|24560.60492|24779.70283
fast|Levels:|East|Yield|21.89870025|23.04397691|24.03425688|24.84955185|25.61995539
fast|YoY %:|East|Revenue $M|.|-10.366965|1.54958578|3.997948032|4.436078429
fast|YoY %:|East|ADV|.|-15.16104766|-2.245085151|0.585854137|0.892070469
fast|YoY %:|East|Yield|.|5.229884164|4.297348405|3.392220431|3.100271361
fast|Levels:|Total|Revenue $M|196.6456516|202.352678|210.0463701|216.0592935|223.2674789
fast|Levels:|Total|ADV|36196.872|35986.86853|35877.48717|35665.94123|35545.70296
fast|Levels:|Total|Yield|21.730679|22.40222618|23.41818078|24.23144166|25.02445567
fast|YoY %:|Total|Revenue $M|.|2.902187956|3.802120246|2.86266475|3.336207025
fast|YoY %:|Total|ADV|.|-0.580170226|-0.303947973|-0.589634219|-0.337123498
fast|YoY %:|Total|Yield|.|3.090318413|4.535060926|3.472775642|3.272665411
fast|YoY Chg:|West|Revenue $M|.|22.417095|5.45491778|0.14735635|0.43961658
fast|YoY Chg:|West|ADV|.|4253.72188|451.40362|-354.59719|-339.33618
fast|YoY Chg:|West|Yield|.|-0.05223303|1.15941957|0.75891251|0.78938931
fast|YoY Chg:|East|Revenue $M|.|-16.7100686|2.2387744|5.865567|6.7685687
fast|YoY Chg:|East|ADV|.|-4463.72535|-560.78498|143.05125|219.09791
fast|YoY Chg:|East|Yield|1.14527666|0.99027997|0.81529497|0.77040354
fast|YoY Chg:|Total|Revenue $M|.|5.7070264|7.6936921|6.0129234|7.2081854
fast|YoY Chg:|Total|ADV|.|-210.00347|-109.38136|-211.54594|-120.23827
fast|YoY Chg:|Total|Yield|.|0.67154718|1.0159546|0.81326088|0.79301401
;
RUN;

I modified the PROC REPORT code to include the data above:

proc format;
value FY20p low -< 0 = 'red';
value FY21p low -< 0 = 'red' ;
value FY22p low -< 0 = 'red';
value FY23p low -< 0 = 'red' ;
run;
proc report data=trial nowd spanrows center;
where prod='fast' and type='West';
column calcs metrics1 FY20 FY21 FY22 FY23;
define calcs / " " group ;
define metrics1 / "Metric:" display ;
define FY20 / analysis 'FY20' format=negparen7.1 style(column)=[foreground=FY20p.];
define FY21 / analysis 'FY21' format=negparen7.1 style(column)=[foreground=FY21p.];
define FY22 / analysis 'FY22' format=negparen7.1 style(column)=[foreground=FY22p.];
define FY23 / analysis 'FY23' format=negparen7.1 style(column)=[foreground=FY23p.];
compute before calcs;
_c=calcs;
endcompute;
compute fy20;
 if metrics1='Rev' and _c='Levels:' then call define(_col_,'format','8.1');
 if metrics1='ADV' and _c='Levels:' then call define(_col_,'format','comma10.0');
 if metrics1='Yield' and _c='Levels:' then call define(_col_,'format','8.2');
 if _c='YoY%' then call define(_col_,'format','10.1');
endcompute;

compute fy21;
if metrics1=:'Rev' and _c='Levels:' then call define(_col_,'format','8.1');
if metrics1='ADV' and _c='Levels:' then call define(_col_,'format','comma10.0');
if metrics1='Yield' and _c='Levels:' then call define(_col_,'format','8.2');
if metrics1='Rev' and _c='YoY Chg:' then call define(_col_,'format','8.1');
if metrics1='ADV' and _c='YoY Chg:' then call define(_col_,'format','comma10.0');
if metrics1='Yield' and _c='YoY Chg:' then call define(_col_,'format','8.2');
if _c='YoY%' then call define(_col_,'format','10.1');
endcompute;

compute fy22;
if metrics1=:'Rev' and _c='Levels:' then call define(_col_,'format','8.1');
if metrics1='ADV' and _c='Levels:' then call define(_col_,'format','comma10.0');
if metrics1='Yield' and _c='Levels:' then call define(_col_,'format','8.2');
if metrics1=:'Rev' and _c='YoY Chg:' then call define(_col_,'format','8.1');
if metrics1='ADV' and _c='YoY Chg:' then call define(_col_,'format','comma10.0');
if metrics1='Yield' and _c='YoY Chg:' then call define(_col_,'format','8.2');
if _c='YoY%' then call define(_col_,'format','10.1');
endcompute;

compute fy23;
if metrics1=:'Rev' and _c='Levels:' then call define(_col_,'format','8.1');
if metrics1='ADV' and _c='Levels:' then call define(_col_,'format','comma10.0');
if metrics1='Yield' and _c='Levels:' then call define(_col_,'format','8.2');
if metrics1=:'Rev' and _c='YoY Chg:' then call define(_col_,'format','8.1');
if metrics1='ADV' and _c='YoY Chg:' then call define(_col_,'format','comma10.0');
if metrics1='Yield' and _c='YoY Chg:' then call define(_col_,'format','8.2');
if _c='YoY%' then call define(_col_,'format','10.1');
endcompute;
run;

But when I view the results, the rows for YoY Chg ADV and YoY Chg Yield are not coming out to zero and 2 decimal places respectively even though I specify this in the code above:

How do I fix this?