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
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;
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
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;
This works great, thanks for the reply!
Jack
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.