Search code examples
sasmacrosconditional-formatting

Conditional Formatting in SAS with Macros

Hi I'm trying to apply conditional formatting to a table in SAS. The conditional formatting will be highlighting data in columns (variables). If the field is "Y" then highlight green, else if the field is "N" then red.

The input table looks like this:

CURRENT INPUT

My goal is to have it looks something like this

DESIRED OUTPUT

I currently have this code in my macro:

%LET NVARS= 6;
%LET to_date= '01SEP2022'd;

%macro cf();
PROC REPORT DATA=work.process /*the data set input */ OUT=work.test1;
COLUMN SKU HIGH PRI COST_CTR 

%DO i = 1 %TO &NVARS.;
SUPPORT&i.
%END;

%DO i =1 %TO &NVARS.;
DEFINE SUPPORT&i. / DISPLAY
                  STYLE (column)={just=center};
%END;

%DO i =1 %TO &NVARS.;
COMPUTE SUPPORT&i.;
IF SUPPORT&i. ='Y' THEN CALL DEFINE (_col_,'style','style={background=vilg}');

IF SUPPORT&i. ='N' THEN CALL DEFINE (_col_,'style','style={background=viypk}');
ENDCOMP;

%END;

RENAME
%DO i =1 %TO &NVARS.;
SUPPORT&i. = %SYSFUNC(INTNX(month,&to_date.,&I-1),monyy7)
%END;

RUN;
%MEND;
%cf();

For some reason it's erroring out and not doing the conditional formatting. I then want to export the output in excel. Any help is greatly appreciates from SAS gurus.

Solution

  • Personally, I would use PROC PRINT to do this instead of PROC REPORT.

    • Create a format to format Y as green, N as red
    • Use PROC PRINT to display information
    • Create labels dynamically instead of rename to display the months name dynamically.
    • Use ODS EXCEL to pipe the formatted output including colours directly to Excel (tested and it works).
    proc format;
value $ support_fmt
'Y' = 'vilg'
'N' = 'viypk';
run;



ods excel file = '/home/fkhurshed/Demo1/demo.xlsx';
%macro cf;
%LET NVARS= 6;
%LET to_date= '01SEP2022'd;


proc print data=have label noobs;
var sku high_pri cost_ctr ;
var support1-support6 / style={background=$support_fmt.};

 %do i=1 %to &nvars;
   label support&i. = %sysfunc(intnx(month, &to_date, %eval(&i-1)), monyy7.);;
   %end;

run;

%mend;

%cf;

ods excel close;