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: 9,878

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

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)

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: 8,820

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

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)

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)

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)

What would I need to change to use the ODS Excel instead of RTF?
SAS Super FREQ
Posts: 302

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

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)

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: 9,878

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

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
  • 358 views
  • 4 likes
  • 5 in conversation