topic Re: conditional formatting of rows through ODS Excel in ODS and Base Reporting

conditional formatting of rows through ODS Excel

KoVa — Tue, 25 Oct 2022 13:36:16 GMT

Hello,

I'm in the process of migrating a reporting table entirely to a SAS output through ODS excel.

So far I have everything I previously had in the reporting layout that was done in excel, except for one thing.

I have a macro that alters the borderline of a row based on the cell value of a certain column (I have attached an example of this).

the report is pre-sorted, the macro just runs through the column and if it's the same value as the previous value (the one above in the sheet) the whole row is give a thin dotted bottomline. if not, there is a normal line (once again for the whole row of data).

I've already found how to de-activate the gridlines, and other layout stuff, but I have yet to find this one... any help much appreciated!

Re: conditional formatting of rows through ODS Excel

PaigeMiller — Tue, 25 Oct 2022 13:49:55 GMT

Many of us will not download attachments, especially Excel files which can be a security threat. Please show us a screen capture of the issue in Excel, use the "Insert Photos" icon (do not attach files).

Also, please show us the macro by copying the entire macro and pasting it into the window that appears when you click on the "little running man" icon.

Re: conditional formatting of rows through ODS Excel

KoVa — Tue, 25 Oct 2022 14:25:01 GMT

as requested here is a screenshot of an example.

the code I use to generate the report is pretty basic

/*create sheet with links to enrollment*/
ODS EXCEL FILE=&linkr style=htmlblue options(
frozen_headers="ON" autofilter = "1-9" row_heights="30,13,13,13,13,13,13" flow = "header, data" 
absolute_column_width = "18,27,12,12,56,10,21,21" 
sheet_name = "&namer"
) ;
 
PROC report DATA=rep_&campaign nowd;
columns col1	col2 col3 col4 col5 link;
define col1 /
     style(header)={just=center }
     style(column)={just=center};
define col2 /
     style(header)={just=center }
     style(column)={just=center};
compute col3;
 call define(_col_,'url',link);
 if substr(col2,1,1) = 'P' THEN CALL DEFINE(_col_, "style", "STYLE=[BACKGROUND=cxFFFF99 textdecoration=underline color=blue]");
 if substr(col2,1,1) = 'A' THEN CALL DEFINE(_col_, "style", "STYLE=[BACKGROUND=lightgreen textdecoration=underline color=blue]");
 if substr(col2,1,1) = 'R' THEN CALL DEFINE(_col_, "style", "STYLE=[BACKGROUND=lightred textdecoration=underline color=blue]");
endcomp;

define link / noprint;
RUN ;

 
ODS _all_ CLOSE;

Re: conditional formatting of rows through ODS Excel

PaigeMiller — Tue, 25 Oct 2022 14:33:02 GMT

Nothing matches your original description. You mentioned a macro, but you don't show a macro. The code you show changes colors, decoration and background colors, but the Excel screen capture shows none of that. Your Excel screen capture shows INFO1 through INFO5, but your code shows none of that.

Please provide screen captures and code that are consistent with each other and consistent with your original explanation.

Re: conditional formatting of rows through ODS Excel

KoVa — Tue, 25 Oct 2022 14:39:58 GMT

since I cannot just past code here I just renamed columns to create 2 examples (yes with different headers and such), sorry.
Anyway, I think I have found what I was looking for by adding a column in a datastep first. should have searched a bit more, because I think I can use this:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Controlling-borders-in-PROC-REPORT-and-ODS-Excel/td-p/755418

Re: conditional formatting of rows through ODS Excel

Cynthia_sas — Tue, 25 Oct 2022 14:51:23 GMT

HI, In addition to @PaigeMiller suggestions, you have a variable called LINK that you are using to create a URL but we don't see that value on the report because you have LINK set to NOPRINT. Also, LINK is at the end of the report row, but you are trying to use it in the COMPUTE block for COL3, which should fail. In addition, based on the data you show, none of your IF statements would work because as far as I can tell, nothing in your COL2 variable has values of 'P', 'A' or 'R'.
Cynthia

Re: conditional formatting of rows through ODS Excel

KoVa — Tue, 25 Oct 2022 19:13:12 GMT

thx but it's already solved, found the answer elsewhere on this forum. should have looked a bit more first 🙂