I've got data like the following:
year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 total _1ind _2ind _3ind _4ind _5ind ... _12ind
2016 5 4 7 2 3 1 3 7 1 3 5 3 44 1 1 1 1 1 1
2017 8 6 4 4 2 3 1 6 3 2 2 2 43 1 1 1 0 0 0
2018 9 7 3 3 4 2 2 2 2 1 3 1 39 0 0 0 0 0 0
I need a proc report with the data from the first 14 columns and i need to color the cells of columns _1 to _12 based on the 1/0 indicators _1ind to _12ind.
And I need a blank column between _12 and total
the 12 columns are months and the coloring is just to differentiate actuals vs predicted, so I could also ignore the 1/0 indicators and just look at something like if year<year(today()) then grey, else if year>year(today()) then pink, and then if year=year(today()) then use the month to set the color.
This is my first time coding proc report but here's what I've gotten so far:
proc report data=_table;
column year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 total color;
define year/display;
define _1/display;
define _2/display;
define _3/display;
define _4/display;
define _5/display;
define _6/display;
define _7/display;
define _8/display;
define _9/display;
define _10/display;
define _11/display;
define _12/display;
define total/display;
define color/computed noprint;
compute color;
if _1ind=0 then do;
call define('_1','style','style={background=pink}');
end;
if _2ind=0 then do;
call define('_2','style','style={background=pink}');
end;
if _3ind=0 then do;
call define('_3','style','style={background=pink}');
end;
if _4ind=0 then do;
call define('_4','style','style={background=pink}');
end;
if _5ind=0 then do;
call define('_5','style','style={background=pink}');
end;
if _6ind=0 then do;
call define('_6','style','style={background=pink}');
end;
if _7ind=0 then do;
call define('_7','style','style={background=pink}');
end;
if _8ind=0 then do;
call define('_8','style','style={background=pink}');
end;
if _9ind=0 then do;
call define('_9','style','style={background=pink}');
end;
if _10ind=0 then do;
call define('_10','style','style={background=pink}');
end;
if _11ind=0 then do;
call define('_11','style','style={background=pink}');
end;
if _12ind=0 then do;
call define('_12','style','style={background=pink}');
end;
endcomp;
run;
I get messages in the log that _1ind to _12ind are uninitialized which I don't understand, they exist on my data set _table.
proc print data=_table;
var _1ind _2ind _3ind _4ind _5ind _6ind _7ind _8ind _9ind _10ind _11ind _12ind;
run;
runs just fine.
And how do I get a blank column in there betweenn _12 and total?
data have;
input year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 total _1ind _2ind _3ind _4ind _5ind ;
cards;
2016 5 4 7 2 3 1 3 7 1 3 5 3 44 1 1 1 1 1 1
2017 8 6 4 4 2 3 1 6 3 2 2 2 43 1 1 1 0 0 0
2018 9 7 3 3 4 2 2 2 2 1 3 1 39 0 0 0 0 0 0
;
run;
proc report data=have nowd;
column year _1-_12 dummy total _1ind _2ind _3ind _4ind _5ind x;
define year/display;
define dummy/computed ' ';
define _1-_12/display;
define _1ind/display noprint;
define _2ind/display noprint;
define _3ind/display noprint;
define _4ind/display noprint;
define _5ind/display noprint;
define x/computed noprint;
compute dummy/char length=20;
dummy=' ';
endcomp;
compute x;
if _1ind=1 then call define('_1','style','style={backgroundcolor=red}');
if _2ind=1 then call define('_2','style','style={backgroundcolor=red}');
if _3ind=1 then call define('_3','style','style={backgroundcolor=red}');
if _4ind=1 then call define('_4','style','style={backgroundcolor=red}');
if _5ind=1 then call define('_5','style','style={backgroundcolor=red}');
endcomp;
run;
I only has done 5 ind , it is easy to extend to 12.
OK, I figured out the blank column. Still need help on the color.
options missing=' ';
proc report data=_table;
column year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 gap total color;
define year/display;
define _1/display;
define _2/display;
define _3/display;
define _4/display;
define _5/display;
define _6/display;
define _7/display;
define _8/display;
define _9/display;
define _10/display;
define _11/display;
define _12/display;
define gap/' ';
define total/display;
define color/computed noprint;
compute color;
if _1ind=0 then do;
call define('_1','style','style={background=pink}');
end;
if _2ind=0 then do;
call define('_2','style','style={background=pink}');
end;
if _3ind=0 then do;
call define('_3','style','style={background=pink}');
end;
if _4ind=0 then do;
call define('_4','style','style={background=pink}');
end;
if _5ind=0 then do;
call define('_5','style','style={background=pink}');
end;
if _6ind=0 then do;
call define('_6','style','style={background=pink}');
end;
if _7ind=0 then do;
call define('_7','style','style={background=pink}');
end;
if _8ind=0 then do;
call define('_8','style','style={background=pink}');
end;
if _9ind=0 then do;
call define('_9','style','style={background=pink}');
end;
if _10ind=0 then do;
call define('_10','style','style={background=pink}');
end;
if _11ind=0 then do;
call define('_11','style','style={background=pink}');
end;
if _12ind=0 then do;
call define('_12','style','style={background=pink}');
end;
endcomp;
run;
Color coding individual cells in proc report rtf
see
https://goo.gl/DYHTr6
https://communities.sas.com/t5/ODS-and-Base-Reporting/proc-report-help-needed-color-cells-based-on-other-value-empty/m-p/345132
see
https://www.dropbox.com/s/gnpbdmpjc77fsqu/mons.rtf?dl=0
If 2016 then pink background for months with a 1 otherwise white background
If 2017 then grey background for months with a 1 otherwise white background
If 2018 then yellow background for months with a 1 otherwise white background
HAVE ( Have 3 observation 2016, 2017, 218. Below is 2016)
===========================================================
Middle Observation(1 ) of have - Total Obs 3
-- NUMERIC -- VALUE
YEAR N 8 2016
_1 N 8 5
_2 N 8 4
_3 N 8 7
_4 N 8 2
_5 N 8 3
_6 N 8 1
_7 N 8 3
_8 N 8 7
_9 N 8 1
_10 N 8 3
_11 N 8 5
_12 N 8 3
TOTAL N 8 44
_1I N 8 1
_2I N 8 1
_3I N 8 1
_4I N 8 1
_5I N 8 1
_6I N 8 1
_7I N 8 1
_8I N 8 1
_9I N 8 1
_10I N 8 1
_11I N 8 1
_12I N 8 1
WANT ( follows the rules above)
===============================
Obs _C1 _C2 _C3
1 ^S={background=lightpink}5 ^S={background=lightpink}4 ^S={background=lightpink}7
2 ^S={background=lightgrey}8 ^S={background=lightgrey}6 ^S={background=lightgrey}4
3 9 7 3
Obs _C4 _C5 _C6
1 ^S={background=lightpink}2 ^S={background=lightpink}3 ^S={background=lightpink}1
2 4 2 3
3 3 4 2
Obs _C7 _C8 _C9
1 ^S={background=lightpink}3 ^S={background=lightpink}7 ^S={background=lightpink}1
2 ^S={background=lightgrey}1 ^S={background=lightgrey}6 ^S={background=lightgrey}3
3 2 2 2
Obs _C10 _C11 _C12
1 ^S={background=lightpink}3 ^S={background=lightpink}5 ^S={background=lightpink}3
2 2 2 2
3 1 3 1
Obs _C1 _C2 _C3 _C4 _C5 _C6 _C7 _C8 _C9 _C10 _C11 _C12
1 5 4 7 2 3 1 3 7 1 3 5 3 ** all pink because 1s in all slots
2 8 6 4 4 2 3 1 6 3 2 2 2 ** _c1-_c2 are grey _c7-_c9 are greal
3 9 7 3 3 4 2 2 2 2 1 3 1 ** backround is all white no color
WORKING CODE
if inds=1 then mids=cats('^S={background=lightpink}',put(mons,3.));
else mids=put(mons,3.);
FULL SOLUTION
* _ _ _
_ __ ___ __ _| | _____ __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \_____ / _` |/ _` | __/ _` |
| | | | | | (_| | < __/_____| (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___| \__,_|\__,_|\__\__,_|
;
data have;
input year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 total
_1i _2i _3i _4i _5i _6i _7i _8i _9i _10i _11i _12i;
array mons _1--_12;
array inds _1i--_12i;
array mids $44 _c1-_c12;
do over mons;
select (year);
when (2016) do;
if inds=1 then mids=cats('^S={background=lightpink}',put(mons,3.));
else mids=put(mons,3.);
end;
when (2017) do;
if inds=1 then mids=cats('^S={background=lightgrey}',put(mons,3.));
else mids=put(mons,3.);
end;
when (2018) do;
if inds=1 then mids=cats('^S={background=lightyellow}',put(mons,3.));
else mids=put(mons,3.);
end;
end;
keep year _c:;
end;
cards4;
2016 5 4 7 2 3 1 3 7 1 3 5 3 44 1 1 1 1 1 1 1 1 1 1 1 1
2017 8 6 4 4 2 3 1 6 3 2 2 2 43 1 1 1 0 0 0 1 1 1 0 0 0
2018 9 7 3 3 4 2 2 2 2 1 3 1 39 0 0 0 0 0 0 0 0 0 0 0 0
;;;;
run;quit;
* _ _ _
___ ___ | |_ _| |_(_) ___ _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|
;
ods escapechar='^';
%utl_rtflan100;
ods rtf file="d:/rtf/mons.rtf" style=utl_rtflan100;;
proc print data=have;
var _c:;
run;quit;
ods rtf close;
*_ _ _
| |__ (_)_ __ | |_
| '_ \| | '_ \| __|
| | | | | | | | |_
|_| |_|_|_| |_|\__|
;
* This might help if you need to use proc report;
proc report data=have list;
run;quit;
* you will see this in the log;
PROC REPORT DATA=WORK.HAVE LS=85 PS=36 SPLIT="/" NOCENTER ;
COLUMN YEAR _C1 _C2 _C3 _C4 _C5 _C6 _C7 _C8 _C9 _C10 _C11 _C12;
DEFINE YEAR / SUM FORMAT= BEST9. WIDTH=9 SPACING=2 RIGHT "YEAR" ;
DEFINE _C1 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C1" ;
DEFINE _C2 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C2" ;
DEFINE _C3 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C3" ;
DEFINE _C4 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C4" ;
DEFINE _C5 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C5" ;
DEFINE _C6 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C6" ;
DEFINE _C7 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C7" ;
DEFINE _C8 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C8" ;
DEFINE _C9 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C9" ;
DEFINE _C10 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C10" ;
DEFINE _C11 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C11" ;
DEFINE _C12 / DISPLAY FORMAT= $44. WIDTH=44 SPACING=2 LEFT "_C12" ;
RUN;
You need this template
%Macro utl_rtflan100
(
style=utl_rtflan100,
frame=box,
rules=groups,
bottommargin=1.0in,
topmargin=1.5in,
rightmargin=1.0in,
cellheight=10pt,
cellpadding = 7,
cellspacing = 3,
leftmargin=.75in,
borderwidth = 1
) / Des="SAS Rtf Template for CompuCraft";
options orientation=landscape;run;quit;
ods path work.templat(update) sasuser.templat(update) sashelp.tmplmst(read);
Proc Template;
define style &Style;
parent=styles.rtf;
replace body from Document /
protectspecialchars=off
asis=on
bottommargin=&bottommargin
topmargin =&topmargin
rightmargin =&rightmargin
leftmargin =&leftmargin
;
replace color_list /
'link' = blue
'bgH' = _undef_
'fg' = black
'bg' = _undef_;
replace fonts /
'TitleFont2' = ("Arial, Helvetica, Helv",11pt,Bold)
'TitleFont' = ("Arial, Helvetica, Helv",11pt,Bold)
'HeadingFont' = ("Arial, Helvetica, Helv",10pt)
'HeadingEmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'StrongFont' = ("Arial, Helvetica, Helv",10pt,Bold)
'EmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'FixedFont' = ("Courier New, Courier",9pt)
'FixedEmphasisFont' = ("Courier New, Courier",9pt,Italic)
'FixedStrongFont' = ("Courier New, Courier",9pt,Bold)
'FixedHeadingFont' = ("Courier New, Courier",9pt,Bold)
'BatchFixedFont' = ("Courier New, Courier",7pt)
'docFont' = ("Arial, Helvetica, Helv",10pt)
'FootFont' = ("Arial, Helvetica, Helv", 9pt)
'StrongFootFont' = ("Arial, Helvetica, Helv",8pt,Bold)
'EmphasisFootFont' = ("Arial, Helvetica, Helv",8pt,Italic)
'FixedFootFont' = ("Courier New, Courier",8pt)
'FixedEmphasisFootFont'= ("Courier New, Courier",8pt,Italic)
'FixedStrongFootFont' = ("Courier New, Courier",7pt,Bold);
replace GraphFonts /
'GraphDataFont' = ("Arial, Helvetica, Helv",8pt)
'GraphAnnoFont' = ("Arial, Helvetica, Helv",8pt)
'GraphValueFont' = ("Arial, Helvetica, Helv",10pt)
'GraphUnicodeFont' = ("Arial, Helvetica, Helv",10pt)
'GraphLabelFont' = ("Arial, Helvetica, Helv",10pt,Bold)
'GraphLabel2Font' = ("Arial, Helvetica, Helv",10pt,Bold)
'GraphFootnoteFont' = ("Arial, Helvetica, Helv",8pt)
'GraphTitle1Font' = ("Arial, Helvetica, Helv",11pt,Bold)
'GraphTitleFont' = ("Arial, Helvetica, Helv",11pt,Bold);
style table from table /
outputwidth=100%
protectspecialchars=off
asis=on
background = colors('tablebg')
frame=&frame
rules=&rules
cellheight = &cellheight
cellpadding = &cellpadding
cellspacing = &cellspacing
bordercolor = colors('tableborder')
borderwidth = &borderwidth;
replace Footer from HeadersAndFooters
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off ;
replace FooterFixed from Footer
/ font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off;
replace FooterEmpty from Footer
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off;
replace FooterEmphasis from Footer
/ font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off;
replace FooterEmphasisFixed from FooterEmphasis
/ font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off;
replace FooterStrong from Footer
/ font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off;
replace FooterStrongFixed from FooterStrong
/ font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off;
replace RowFooter from Footer
/ font = fonts('FootFont') asis=on protectspecialchars=off just=left;
replace RowFooterFixed from RowFooter
/ font = fonts('FixedFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmpty from RowFooter
/ font = fonts('FootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmphasis from RowFooter
/ font = fonts('EmphasisFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterEmphasisFixed from RowFooterEmphasis
/ font = fonts('FixedEmphasisFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterStrong from RowFooter
/ font = fonts('StrongFootFont') just=left asis=on protectspecialchars=off;
replace RowFooterStrongFixed from RowFooterStrong
/ font = fonts('FixedStrongFootFont') just=left asis=on protectspecialchars=off;
replace SystemFooter from TitlesAndFooters / asis=on
protectspecialchars=off just=left;
end;
run;
quit;
%Mend utl_rtflan100;
The ODS style overrides in the solution proposed by Ksharp should work for the HTML, RTF, PDF, Excel, and tagsets.ExcelXP destinations.
Vince DelGobbo
SAS R&D
I tried this but I still get error messages about all of the fields being uninitialized.
options missing=' ';
proc report data=_table;
column year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 gap total _1ind _2ind _3ind _4ind _5ind _6ind _7ind _8ind _9ind _10ind _11ind _12ind color;
define year/display;
define _1/display;
define _2/display;
define _3/display;
define _4/display;
define _5/display;
define _6/display;
define _7/display;
define _8/display;
define _9/display;
define _10/display;
define _11/display;
define _12/display;
define gap/' ';
define total/display;
define _1ind/noprint;
define _2ind/noprint;
define _3ind/noprint;
define _4ind/noprint;
define _5ind/noprint;
define _6ind/noprint;
define _7ind/noprint;
define _8ind/noprint;
define _9ind/noprint;
define _10ind/noprint;
define _11ind/noprint;
define _12ind/noprint;
define color/computed noprint;
compute color;
if _1ind=0 then do;
call define('_1','style','style={background=pink}');
end;
if _2ind=0 then do;
call define('_2','style','style={background=pink}');
end;
if _3ind=0 then do;
call define('_3','style','style={background=pink}');
end;
if _4ind=0 then do;
call define('_4','style','style={background=pink}');
end;
if _5ind=0 then do;
call define('_5','style','style={background=pink}');
end;
if _6ind=0 then do;
call define('_6','style','style={background=pink}');
end;
if _7ind=0 then do;
call define('_7','style','style={background=pink}');
end;
if _8ind=0 then do;
call define('_8','style','style={background=pink}');
end;
if _9ind=0 then do;
call define('_9','style','style={background=pink}');
end;
if _10ind=0 then do;
call define('_10','style','style={background=pink}');
end;
if _11ind=0 then do;
call define('_11','style','style={background=pink}');
end;
if _12ind=0 then do;
call define('_12','style','style={background=pink}');
end;
endcomp;
run;
data have;
input year _1 _2 _3 _4 _5 _6 _7 _8 _9 _10 _11 _12 total _1ind _2ind _3ind _4ind _5ind ;
cards;
2016 5 4 7 2 3 1 3 7 1 3 5 3 44 1 1 1 1 1 1
2017 8 6 4 4 2 3 1 6 3 2 2 2 43 1 1 1 0 0 0
2018 9 7 3 3 4 2 2 2 2 1 3 1 39 0 0 0 0 0 0
;
run;
proc report data=have nowd;
column year _1-_12 dummy total _1ind _2ind _3ind _4ind _5ind x;
define year/display;
define dummy/computed ' ';
define _1-_12/display;
define _1ind/display noprint;
define _2ind/display noprint;
define _3ind/display noprint;
define _4ind/display noprint;
define _5ind/display noprint;
define x/computed noprint;
compute dummy/char length=20;
dummy=' ';
endcomp;
compute x;
if _1ind=1 then call define('_1','style','style={backgroundcolor=red}');
if _2ind=1 then call define('_2','style','style={backgroundcolor=red}');
if _3ind=1 then call define('_3','style','style={backgroundcolor=red}');
if _4ind=1 then call define('_4','style','style={backgroundcolor=red}');
if _5ind=1 then call define('_5','style','style={backgroundcolor=red}');
endcomp;
run;
I only has done 5 ind , it is easy to extend to 12.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.