coloring particular cells in a table that are not computed. 1

[yuthika]

Hi alL,

I am reposting my question rephrased as no one has answered yet. After going through few posts on proc report in the forum i realized i cannot color particular cell in table but luckily it can be done using ods excelxp tagset.

I started using sas recently so i really dont have any clue how it works. Some example codes would help me understand better. I wrote a simple example of the code;

data XYZ;
input alg ref count;
datalines;
1 1 12
1 2 11
1 3 4
2 1 3
3 2 19
2 3 10 ;
run;

proc report data = xyz spilt ='\'
style(report) = [rules=all
cellspacing=0
bordercolor=black];
col alg ref,(count);
define alg / group;
define ref/ across;
define count/ sum '\';
run;

I cannot compute any thing on count but i need to color the cell with similar alg amd ref . so here my cell holding 12 as count will be colored and rest will have missing values but cells has to be colored giving a diagonal look.

Please help.


( I came across few posts answered by chris pretty close to what i need. Thankyou chris)

 

[ChrisW75]

Hi yuthika,
this should give you some pretty comprehensive insights into how to use colour in Proc Report:-

http://www2.sas.com/proceedings/forum2008/224-2008.pdf

There's a section on changing colours in a specific cell. The examples mention just ODS PDF, but it should work the same with ODS TAGSETS.EXCELXP.

I just did a google search on "SAS examples proc report color cell", if you do the same thing, you should find a bunch of other examples.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[yuthika]

Hi chris,

Thank you for your time.

My problem is little more complicated than coloring the cells itself. If you notice in the codes in the paper they have a criteria set for coloring for ex:
COMPUTE RiskAdj;
IF _c5_="WORSE THAN BASELINE" THEN CALL DEFINE("_c6_", "style",
"STYLE=[BACKGROUND=lightRED]");

I actually madeup the example i gave for an ref.
but in my code count is not computed and i need only specific cells to be colored. My other limitations are alg and ref columns in original dataset are dynamic change from drug to drug and i cannot use them as variable names for coloring.

I don't know if i explained myself right.
can you please run the code i have and suggest me on how i can color only my diagonal columns across the table. May be that can help.

Thank you once again for your reply.

yuthika.

 

[ChrisW75]

I'm afraid I can't think of a way of colouring the diagonal cells easily. The compute step you've shown there is the way to selectively colour the cells.
Because you're doing a cross-tab, it's a bit more tricky, if you had a simple table structure where each column was a different variable, you could do a different compute for each column, check the value and set the colour accordingly, that's the only way I could of to try.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[yuthika]

Thankyou chris,
I was aware of this limitations when i started to work on this report.I figured may be if i make my coulmn names into a &varlist and call it in the compute block for check it would work. But i end up with error saying the macro variables cannot be resolved.

Is there a way to use macro variables in compute block? If so how? My code looked like below.
proc report data = toutset1 nowd split = '\'
style(report) = [rules=all bordercolor=gray];
column calgmic &varlist;
define calgmic / 'Reference MIC-->\Algorithm MIC' width=10 ;
define &varlist /across width=10;
compute "&varlist";

%do i = 1 %to &nummic;
if _C(&i)_ = _C(&i+1)_ then
call define("_C(&i)_",'style','style=[background=black foreground=white]');

endcomp;
%end;
run;

Here the nummic is the number of columns and &varlist is the column names in required order.

This reporting is on my table for last 3 months.They really want to do it as it will bring in some funds for the team.I would really appreciate if you can come up with "trick the sas kind of stuff". I dont mind how long the code is, it dont have to be easy enough.

Anything you could think off.

Thankyou

yuthika