ods output styles color coding

[eawh100]

Hello,

 I currently have the top table in excel from proc print ods excel output. The only column I want to focus on is the last column "DESC".

I want the rows to alternate colors based on description.   See second table for an idea of the output I want. I couldnt include color fill on here, so I bolded the font to illustrate. But what I want really is the first two rows should be say blue, then since description on row 3 changes it is now grey, row 4 also grey, then row 5,6,7 are back to blue. and so on and so forth. 

I have no idea how to start this. Obviously, the data set is much larger, so doing it manually in excel would be time consuming. Was wondering if proc report would be the place to start and how?

id	INITIALS	code	var2	START_DATE	DESC
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	c
a	GT	1	na	19Nov2023	c
b	hi	1	.	22Nov2023	d
b	hi	1	.	10Nov2023	d
b	hi	1		10Nov2023	d
a	GT	1	.	10Nov2023	f

id	INITIALS	code	var2	START_DATE	DESC
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	c
a	GT	1	na	19Nov2023	c
b	hi	1	.	22Nov2023	d
b	hi	1	.	10Nov2023	d
b	hi	1		10Nov2023	d
a	GT	1	.	10Nov2023	f

[ballardw · Accepted solution]

I suspect this quite easy for Proc Report output.

Consider:

data have;
   input id $	INITIALS $	code 	var2 $	START_DATE :Date9. DESC $;
   format start_date date9.;
datalines;
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	c
a	GT	1	na	19Nov2023	c
b	hi	1	.	22Nov2023	d
b	hi	1	.	10Nov2023	d
b	hi	1	. 	10Nov2023	d
a	GT	1	.	10Nov2023	f
;

data helpful;
   set have;
   by desc notsorted;
   length color $ 10;
   Retain color;
   if first.desc then do;
      if color='lightblue' then color='lightgray';
      else color='lightblue';
   end;
run;

proc report data=helpful;
   columns color id INITIALS code var2	START_DATE DESC;
   define color /noprint;
   compute color;
      call define (_row_,"style","style=[backgroundcolor="||color||"]");
   endcomp;
run;

This has alternating rows of background color set in the Helpful data set.

If you want to use a longer color name, such as "verylightblue" or "verylightgray" then make the Length of the Color variable larger to hold all of the characters.

How this works:

The By statement in the Helpful data set adds automatic variables First.variable and Last.variable to test allow testing membership changes in the By variable(s) values. The NOTSORTED option means that the data does not have to be sorted.

The RETAIN keeps a setting for color across data step boundaries.

The IF First tests to see what the current value of color is and switches it for the first observation of the next group of Desc values.

The Color variable is used in the report with NOPRINT so it doesn't appear in the in report. But that does allow use of a compute block with that variable to set the row level background color.

If you haven't seen the use of || that is string concatenation operator so the result of "something="||color||"]" is

"something=lightblue]". Just one way to create a valid value in the Call Define statement.

[eawh100]

Thank you for this breakdown. That is exactly what I needed to see! Your middle data step is what I was missing.  I got caught up trying to figure something out directly in proc report. Thanks!

[Cynthia_sas]

Hi:

  In the interest of completeness, here's a similar solution, but not using a DATA step to set the color. Instead, this specifies a user-defined format in the COMPUTE block for DESC, as shown below:

data fakedata;
   input id $	INITIALS $	code 	var2 $	START_DATE :Date9. DESC $;
   roword = _n_;
   format start_date date9.;
datalines;
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	c
a	GT	1	na	19Nov2023	c
b	hi	1	.	22Nov2023	d
b	hi	1	.	10Nov2023	d
b	hi	1	. 	10Nov2023	d
a	GT	1	.	10Nov2023	f
;


proc format;
  value $dfmt 'h'='lightblue'
              'c'='lightgray'
			  'd'='lightblue'
			  'f'='lightgray';
run;

proc report data=fakedata;
   columns roword id INITIALS code var2	START_DATE DESC;
   define roword / order order=data noprint;
   define id / display;
   define initials / display;
   define code / display;
   define var2 / display;
   define start_date /display;
   define desc / display;
   compute desc;
      call define (_row_,"style","style=[backgroundcolor="||put(desc,$dfmt.)||"]");
   endcomp;
run;

  When I have reports that are displayed in the original order, where sorting or ordering may change the order, I like to create a "ROWORD" variable when reading the data in, so that my PROC REPORT can use that to ensure the rows are in the desired order. Also, BY group processing may not always be an option. If there were other values for DESC, they could be added to the PROC FORMAT step. Or other colors could be used -- a different color for each value of DESC.

Cynthia

[eawh100]

Hi! Thanks for this. So my dataset was a short version of what I would be working with, so lots more options may appear in the DESC columns. I feel like making a format for a longer dataset would be quite time consuming? But this is helpful to know.

[Cynthia_sas]

Hi: if you just want alternating colors without regard to the values for DESC, then the first approach is probably appropriate. However, the format approach is handy to know.
Cynthia

[ballardw]

---

@eawh100 wrote:

Hi! Thanks for this. So my dataset was a short version of what I would be working with, so lots more options may appear in the DESC columns. I feel like making a format for a longer dataset would be quite time consuming? But this is helpful to know.

---

Once you learn that Proc Format will use a data set with certain special variables maybe not.

data work.have;
   input id $	INITIALS $	code 	var2 $	START_DATE :Date9. DESC $;
   format start_date date9.;
datalines;
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	h
a	GT	1	na	17Nov2023	c
a	GT	1	na	19Nov2023	c
b	hi	1	.	22Nov2023	d
b	hi	1	.	10Nov2023	d
b	hi	1	. 	10Nov2023	d
a	GT	1	.	10Nov2023	f
;

Proc sql;
   create table work.desc as
   select distinct desc
   from work.have
   ;
run;

data work.make_desc_format;
   set work.desc (rename=(desc=start)) nobs=ob;

   r = floor( ( _n_/ob)*255);
   b = mod(r+25,255);
   g = mod(r+100,255);
   fmtname= "dfmt";
   type='C';
   label=cats("CX",put(r,Hex2.),put(g,Hex2.),put(b,Hex2.));

run;

proc format library=work cntlin=work.make_desc_format;
run;

proc report data=work.have;
   columns id INITIALS code var2	START_DATE DESC;
   define id / display;
   define initials / display;
   define code / display;
   define var2 / display;
   define start_date /display;
   define desc / display;
   compute desc;
      call define (_row_,"style","style=[backgroundcolor="||put(desc,$dfmt.)||"]");
   endcomp;
run;

What the above does.

First get a set with the unique values of Desc.

Then add variables to allow creating a format similar to the one @Cynthia_sas wrote by hand.

The key variables are FMTNAME, TYPE C or N for character numeric, START (the values of the variable) and Label what to display for the value of Start.

I used an RGB color scheme which uses a string value of CX followed by 3 Hex values of 0 to 255. The OB variable from the NOBS option allows creating a ratio of the current observation number, _n_ and the number of observations in the data set to distribute the values into ranges. The *255 turns the ratio into a number in the range of R (G or B) values of 0 to 255. The + 25 and +100 combined with the MOD function return "scattered" values of G and B for a given R. If all the R, G and B are the same then you get a gray shading scale.

Note that depending on the number of values of DESC you may end up with RGB results that are too dark to read the values. Other naming schemes are available HSV, HLS, CMYK that could use different ranges of ratio and range calculations for the color.

Or the SAS registry has the defined named  colors you could pull such as with Proc Surveyselect to get a random set of names and match to your Start variable values.

The online help on Color-Naming Schemes has much helpful information for creating valid color values.