The SAS Output Delivery System and reporting techniques

proc report help needed (color cells based on other value; empty column)

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

proc report help needed (color cells based on other value; empty column)

[ Edited ]

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?


Accepted Solutions
Solution
‎03-29-2017 10:37 AM
Super User
Posts: 10,313

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to ANWZimmerman
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.

 

x.png

View solution in original post


All Replies
Contributor
Posts: 29

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to ANWZimmerman

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;

SAS Super FREQ
Posts: 9,029

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to ANWZimmerman
PROC REPORT can only "see" the variables that are listed in the COLUMN statement. Your column statement does NOT have _1ind appearing, so from the standpoint of PROC REPORT the variable is not there to be tested. The 0/1 "ind" items need to be on the COLUMN statement and then you can use NOPRINT to "hide" them from the final report.

cynthia
Valued Guide
Posts: 505

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to Cynthia_sas
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;





Valued Guide
Posts: 505

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to rogerjdeangelis
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;
Contributor
Posts: 29

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to rogerjdeangelis
What would I need to change to use the ODS Excel instead of RTF?
SAS Super FREQ
Posts: 333

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to ANWZimmerman

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

 

Contributor
Posts: 29

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to Cynthia_sas

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;

 

Solution
‎03-29-2017 10:37 AM
Super User
Posts: 10,313

Re: proc report help needed (color cells based on other value; empty column)

Posted in reply to ANWZimmerman
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.

 

x.png

Contributor
Posts: 29

Re: proc report help needed (color cells based on other value; empty column)

Thanks. Looks like my main issue was that i needed "display noprint" and not just "noprint".
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 368 views
  • 4 likes
  • 5 in conversation