Proc Report - change cell colour based on value

[MART1]

Hello everyone

I'm trying to colour code some values on Proc Report using compute, but I can't get it right and cannot understand why it's not working.

Based on the example below, I'd need RCVD to change colour based on the value of NEWC

data TEST;                                                                                                                            
   input WD EXPD RCVD RCVD_ALL NEWC $;                                                                                                                 
   datalines;    

-1 49 49 56 Y
01 26 26 77 Y
02 77 45 0 N
03 71 0 0 N
04 44 0 0 N
;
RUN;

proc report data=TEST missing nowd;
	column  (WD, ( EXPD RCVD RCVD_ALL));
	define WD / 'Working Day' across  NOZERO ORDER=DATA;
	define EXPD / analysis 'Expd' missing;
	define RCVD / analysis 'Rcvd' missing;
	define RCVD_ALL / analysis 'Rcvd all' missing;


	compute RCVD;

		if NEWC = "Y" then
			do;
				call define (_col_,"style", "style={background=green}");
			end;

		else if NEWC = "N" then
			do;
				call define (_col_,"style", "style={background=red}");
			end;
	endcomp;
run;
quit;

ps: I cannot indicate specific columns (i.e. _c1_ etc as the table can expand or contract based on the values received)

Many thanks

[PaigeMiller · Accepted solution]

Many papers have been written on this, for example http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

[ballardw]

The first thing is that you are referencing a variable, NEWC, that does not appear anywhere in your column definitions.

You may have to 1) include NEWC in the columns definition, 2) Before (to the left of) RCVD_all to be used and 3) have the NOPRINT role assigned to it in a define.

But with the structure you are using with no row grouping you may have a hard time setting a role for NEWC that maintains the appearance of your table.

[PaigeMiller]

Now that I have had some time to work on this, here is code that works (for a few of your columns, anyway)

proc format;
    invalue ynf 'Y'=1 'N'=0;
run;
data TEST;                                                                                                                            
   input WD EXPD RCVD RCVD_ALL NEWC ynf.;                                                                                                                 
   datalines;    

-1 49 49 56 Y
01 26 26 77 Y
02 77 45 0 N
03 71 0 0 N
04 44 0 0 N
;
RUN;

proc report data=TEST missing nowd out=_t_;
	column  WD, (newc EXPD RCVD RCVD_ALL);
	define newc / noprint;
	define WD / 'Working Day' across  NOZERO ORDER=DATA;
	define EXPD / analysis 'Expd' missing;
	define RCVD / analysis 'Rcvd' missing;
	define RCVD_ALL / analysis 'Rcvd all' missing;
	compute rcvd;
		if _c1_ = 1 then do;
				call define (3,"style", "style={background=green}");
		end;
		else if _c1_ = 0 then do;
				call define (3,"style", "style={background=red}");
		end;
		if _c5_ = 1 then do;
				call define (7,"style", "style={background=green}");
		end;
		else if _c5_ = 0 then do;
				call define (7,"style", "style={background=red}");
		end;
		if _c9_ = 1 then do;
				call define (11,"style", "style={background=green}");
		end;
		else if _c9_ = 0 then do;
				call define (11,"style", "style={background=red}");
		end;
	endcomp;
run;

[MART1]

Hi  @PaigeMiller 

thanks for this

(ps: I inadvertently pressed the "Accept as Solution" without even reading the post!!)

I did have a look at the paper - and indeed at what you propose - however my issue is that I cannot indicate specific columns using _C1_ etc, as the table can expand or contract based on the values received.

Many thanks 

[PaigeMiller]

---

@MART1 wrote:

however my issue is that I cannot indicate specific columns using _C1_ etc, as the table can expand or contract based on the values received.

---

I don't know what this means. It almost sounds as if you want a PROC REPORT code when you don't even know how many variables you are going to use in PROC REPORT. Please explain further.

[MART1]

Hi @PaigeMiller 

So, the data I provided is an example; the real dataset contains about 22 rows (WD are working days).

NEWC will not always have a value different from 0 (and when it's 0, it won't show in the report, which is fine).

I can't say how many times NEWC will have a value <>0 (as data comes in daily), therefore I cannot say how many variables (or _C_) my report will have.

Hope it makes sense; I'm fairly new with Proc Report and SAS in general, so I appreciate I may want something it cannot be achieved.

thanks

[PaigeMiller]

Well, it seems like you will need a macro to handle this type of varying data. 

OR 

Why does the report have to be in the format you show? Your original data is in a great format for a report, and then you don't need a macro at all, it seems like PROC PRINT ought to produce a reasonable format with little effort.

[MART1]

Well this will be part of a suite of reports that all have the same format (WD across etc), so I was hoping to keep the same formatting.

I never used Proc Print before but I assume I would be able to replicate it - I guess I could transpose WD to show it across.

However this will show all RCVD_ALL even when the value is 0, which is what I was trying to avoid.

[PaigeMiller]

If you have no choice in the report format, then you are stuck with writing a macro to get PROC REPORT to handle all of the different rows and columns as they appear. Not simple.

[ballardw]

---

@MART1 wrote:

Well this will be part of a suite of reports that all have the same format (WD across etc), so I was hoping to keep the same formatting.

I never used Proc Print before but I assume I would be able to replicate it - I guess I could transpose WD to show it across.

However this will show all RCVD_ALL even when the value is 0, which is what I was trying to avoid.

---

You may want to investigate the Report Writing Interface. This is an extension of the Data step that allows you more cell level control of appearance. However, you would likely have to transpose your data to get the equivalent of the Across behavior of your WD variable. In effect delving into the raw coding began Proc Report.

[MART1]

Thanks @ballardw  and @PaigeMiller  for your suggestions.

Report Writing Interface seems a very interesting world to delve in and will do hopefully soon; in the interest of time however I'll have to make the most with what I know (which is not a lot!).

The quickest option for now is to keep the Proc report,  so I can use the _C_ to get the colouring.

I will add the variables to all days (even if they show 0). Can I ask one more question please?

I'm trying to get it as shown below:

However I can't get the two across variables (WD and DAY) to display like this (DAY should be "grouped")

This is what it looks like instead:

code as below:

this is what data TEST;                                                                                                                            
   input WD DAY $ EXPD RCVD RCVD_ALL NEWC $;                                                                                                                 
   datalines;    

-1 31JUL 49 49 56 Y
01 3AUG 26 26 77 Y
02 4AUG 77 45 0 N
03 5AUG 71 0 0 N
04 6AUG 44 0 0 N
;
RUN;

proc report data=TEST  nowd;
	column  (WD, DAY, (EXPD RCVD RCVD_ALL));
	define WD / 'Working Day' across  ORDER=DATA ;
	define DAY / ' Day' across  ORDER=DATA ;
	define EXPD / analysis 'Expd';
	define RCVD / analysis 'Rcvd';
	define RCVD_ALL / analysis 'Rcvd all';
quit;

(I've been playing with PRELOADFMT and COMPLETECOLS (following this paper) but I can't get it work)

Many thanks

[PaigeMiller]

This works:

proc report data=TEST  nowd nocompletecols;
	column  (WD, DAY, (EXPD RCVD RCVD_ALL));
	define WD / 'Working Day' across  ORDER=DATA ;
	define DAY / ' Day' across  ORDER=DATA ;
	define EXPD / analysis 'Expd';
	define RCVD / analysis 'Rcvd';
	define RCVD_ALL / analysis 'Rcvd all';
quit;

[MART1]

I see, I didn't come across NOCOMPLETECOLS

it's perfect, many thanks for your help @PaigeMiller