Having trouble with conditional color formatting using Proc Report

[impostersyn]

Hello,

I am new to Proc Report in SAS and have hit a wall with this code.

I have a dataset and want to output an excel file with color coded cells. This data will be changing unpredictably so having the code in the most dynamic format will be the most helpful.

/**Bring in Data**/
DATA TEST;
LENGTH VAR2 $12. VAR3 $20. VAR7 $30.;
LENGTH VAR1 4. VAR4 6. VAR5 6. VAR6 6.;
INPUT VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7;
DATALINES;
0000   XXX   XXXX   300   500.2   600   "Apr 10th, 2024 (Wednesday)" 
0000   XXX   XXXX   50.2   100.5   70   "Apr 9th, 2024 (Tuesday)" 
0000   XXX   XXXX   10   50   5   "Apr 8th, 2024 (Monday)" 
0001   XXX   XXXX   300.1   600.8   598 "Apr 10th, 2024 (Wednesday)"
0001   XXX   XXXX   80   110   90   "Apr 9th, 2024 (Tuesday)"
0001   XXX   XXXX   -20.3   30   10   "Apr 8th, 2024 (Monday)"
0002   XXX   XXXX   400   900   0   "Apr 10th, 2024 (Wednesday)"
0002   XXX   XXXX  50   70.9   100   "Apr 9th, 2024 (Tuesday)"
0002   XXX   XXXX   100   350.5   320   "Apr 8th, 2024 (Monday)"
0003   XXX   XXXX   60   180   80   "Apr 10th, 2024 (Wednesday)"
0003   XXX   XXXX   400   800   700   "Apr 9th, 2024 (Tuesday)"
0003   XXX   XXXX   10   60.2   61   "Apr 8th, 2024 (Monday)"
0004   XXX   XXXX   150   350   140   "Apr 10th, 2024 (Wednesday)"
0004   XXX   XXXX   70   130   80   "Apr 9th, 2024 (Tuesday)"
0004   XXX   XXXX   120   360   600   "Apr 8th, 2024 (Monday)" 
;
RUN;

/*CREATE ALERTS*/
DATA TEST2;
SET TEST1;
IF VAR6= 0 THEN ALERT=1;
ELSE IF VAR6< VAR4 THEN ALERT=2;
ELSE IF VAR4<= VAR6<= VAR5 THEN ALERT=3;
ELSE IF VAR6 > VAR5 THEN ALERT=4;
RUN;

/*OUTPUT*/
ODS EXCEL FILE = "filepath.xlsx";
PROC REPORT DATA=TEST2 NOWDS SPANROWS;
COLUMNS VAR1 VAR2 VAR3 ALERT ('' VAR7), (VAR6 );
     DEFINE VAR1 / GROUP CENTER ORDER=DATA;
     DEFINE VAR2 / GROUP;
     DEFINE VAR3 / GROUP;
     DEFINE ALERT / NOPRINT;
     DEFINE VAR6 / ACROSS;
     DEFINE VAR7 / ACROSS;

COMPUTE VAR6;
   IF ALERT=1 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightred}"); END;
   ELSE IF ALERT=2 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightorange}"); END;
   ELSE IF ALERT= 3 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightgreen}"); END;
   ELSE IF ALERT=4 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightblue}"); END;
ENDCOMP;
RUN;

OD EXCEL CLOSE;

I keep getting a warning that Alert is uninitialized and nothing gets color coded.

The format and grouping is working, but that's it.

This is the intended Excel output.

Any help is appreciated! Thank you!

[rudfaden]

There is servral issues. 

One, you can not use a variable in proc report in compute befroe it is defined in a column. So ALERT needs to be after VAR6. You can fix this with a alias of alert.

Another issue, is the since you group by var1, var2 and var3, alert is also grouped. You can se this if you output the proc report data.

This works, but i gues it is not what you want.

PROC REPORT DATA=TEST2 SPANROWS out=test;
COLUMNS VAR1 VAR2 VAR3 ALERT=ARLET_ALIAS ('' VAR7), (VAR6 ) ALERT;
     DEFINE VAR1 / GROUP CENTER ORDER=DATA;
     DEFINE VAR2 / GROUP;
     DEFINE VAR3 / GROUP;
     DEFINE ALERT / NOPRINT ;
     DEFINE VAR6 / ACROSS;
     DEFINE VAR7 / ACROSS;

COMPUTE VAR6;
   IF ARLET_ALIAS=1 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightred}"); END;
   ELSE IF ARLET_ALIAS=9 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightorange}"); END;
   ELSE IF ARLET_ALIAS= 10 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightgreen}"); END;
   ELSE IF ARLET_ALIAS=8 THEN DO;
         CALL DEFINE (_col_,"style","style={background=lightblue}"); END;
ENDCOMP;
RUN;

[ballardw]

Strong suggestion: Do not place date information in character variables if you ever want to apply an order to the  resulting values.

Use an actual SAS date value (numeric number of days since 1 Jan 1960) and a format to display the information.

Except for the "th" (and implied "st" "nd" or "rd" which I personally find unprofessional in appearance with dates) you can get very similar date text using a custom picture format. I am assuming the day of the week text is critical so create a longer version of the SAS Worddatew. format

Proc format library=work;
picture longworddate (default=25)
low-high ='%3b %d, %Y (%F)' (datatype=date);
run;

data example;
  x=today();
  format x longworddate.;
run;

The picture format directives, the bits that start with % above are: %3b 3-character month abbreviation with mixed case, %d is the numeric day of the month, %Y 4-digit year, %F day of week text. The other characters of space, comma and parentheses are used as they appear.

Example (after getting a working data set) the across order for the given values of Var7 (really need better names) is:

Apr 10th, 2024 (Wednesday)													Apr 8th, 2024 (Monday)													Apr 9th, 2024 (Tuesday)

This is because 1 in a character value comes before 8. Using a two-digit day with leading 0 would allow the dates to sort in day of month order but will typically not appear as desired if your dates cross a month boundary. Consider March 30 to Apr 3 for a report. The data would sort Apr 1, Apr 2, Apr 3, Mar 30, Mar 31 .

If the values are actual dates they would appear in increasing order by default or you could provide instructions to have then in descending order. But with character values you are going to have problems with order.