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!
... View more
