Conditional Coloring for PROC REPORT with ACROSS variable

[DanSturgeon]

I am trying to do simple traffic lighting in a PROC REPORT using a cutoff that is in another variable across variables that are in an ACROSS statement.

proc report data=combined2 nowd style(header)={background=green};

column  NAME MEAS_DESC,MEAS_SCORE;

DEFINE NAME / '' GROUP ORDER=Data;

DEFINE MEAS_DESC / '' ACROSS ORDER=DATA style(header)=vertical_header;

DEFINE meas_score / '' ;

run;

There is another variable 'Target" that can be added which is the target I want to compare them to.

I tried comparing the numbers in a compute statement which did not work then I tried to create a variable which already had the logic within it (if score < target then a = 1) and put that into a compute as well. That did not work.

Is it possible to do this in normal code or will I need to write the macro that I am trying to avoid?

Edits made to simplify question

Message was edited by: Dan Sturgeon

[Cynthia_sas]

Hi:

There are some examples of this in my SGF paper from this year. You have a lot of things to figure out. For one, in order to successfully use your TARGET variable in the COMPUTE block, you MUST put it in the COLUMN statement, even if it is a COMPUTED item. Then, when you are using ACROSS usages, you have to understand how PROC REPORT assigns absolute column numbers to the items under the across item. There have been quite a few previous forum postings on this topic. I know, because quite a few of them were my responses to postings. Here's the paper, http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf there's a reference section at the back. I would not recommend going down the macro road until you understand exactly what it is that PROC REPORT wants in the COMPUTE block and you get your code working without using macro code.

cynthia

[DanSturgeon]

Thanks Cynthia and Scott,

funny enough when you mentioned reading your paper I was literally holding onto it, haha. My code I listed wasn't totally representative of what I actually had, I did have the target in the column statement. My issue ended up being that in the call define statement I was not specifically calling out the specific column, but instead doing a less specific _Col_.

So that was the issue, but now I am good. Thank you very much.

Scott, great tip on the columns I was just counting them. You saved me some time,,,haha.

Thanks to the both of you for your help.

[Scott_C_Moore]

A great way to figure out what PROC REPORT is doing with your variables is to put OUT=datasetnameofyourchoice on the PROC REPORT line and you will have a SAS dataset showing the absolute column numbers for all of your output that you can use in compute blocks to assign formats, colors, etc. for traffic lighting.