DATA Step, Macro, Functions and more

proc report with color coding

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

proc report with color coding

 

I want to color coding "Value" column. Why the blow code does not work?

 

Thanks

 

data a;
input TRT $ Visno $ Param $ value flg;
datalines;
TRT1 v1 A 8 1
TRT1 v1 B 6 0
TRT1 v1 C 5 2
TRT1 v2 A 6 0
TRT1 v2 B 7 0
TRT1 v3 C 8 0
TRT2 v1 A 3 1
TRT2 v1 B 7 3
TRT2 v1 C 5 2
TRT2 v2 A 10 0
TRT2 v2 B 6 1
TRT2 v3 C 10 0
TRT3 v1 A 10 2
TRT3 v1 B 16 1
TRT3 v1 C 18 2
TRT3 v2 A 14 1
TRT3 v2 B 11 2
TRT3 v3 C 15 1
;
proc report data=a NOCOMPLETECOLS;
column trt visno, (param, (flg value ));

define trt / group;
define visno / across;
define param / across;
define flg / display;

compute value;
if flg=1 and above>0 then call define(_col_,"style","style=[background=yellow]");
else if flg=2 then call define(_col_,"style","style=[background=orange]");
else if flg=3 then call define(_col_,"style","style=[background=red]");
endcomp;
run;


Accepted Solutions
Solution
‎09-27-2017 12:07 PM
Super User
Posts: 10,046

Re: proc report with color coding

Posted in reply to Niugg2010
use option OUT= to check the real variable name you should refer to.




data a;
input TRT $ Visno $ Param $ value flg;
datalines;
TRT1 v1 A 8 1
TRT1 v1 B 6 0
TRT1 v1 C 5 2
TRT1 v2 A 6 0
TRT1 v2 B 7 0
TRT1 v3 C 8 0
TRT2 v1 A 3 1
TRT2 v1 B 7 3
TRT2 v1 C 5 2
TRT2 v2 A 10 0
TRT2 v2 B 6 1
TRT2 v3 C 10 0
TRT3 v1 A 10 2
TRT3 v1 B 16 1
TRT3 v1 C 18 2
TRT3 v2 A 14 1
TRT3 v2 B 11 2
TRT3 v3 C 15 1
;
proc report data=a out=x  nowd NOCOMPLETECOLS;
column trt visno,param,(flg value );
define trt / group;
define visno / across;
define param / across;
define flg / analysis sum;
define value/analysis sum;
compute value;
if _c2_=1  then call define('_c3_',"style","style=[background=yellow]");
else if _c2_=2 then call define('_c3_',"style","style=[background=orange]");
else if _c2_=3 then call define('_c3_',"style","style=[background=red]");

if _c4_=1  then call define('_c5_',"style","style=[background=yellow]");
else if _c4_=2 then call define('_c5_',"style","style=[background=orange]");
else if _c4_=3 then call define('_c5_',"style","style=[background=red]");

if _c6_=1  then call define('_c7_',"style","style=[background=yellow]");
else if _c6_=2 then call define('_c7_',"style","style=[background=orange]");
else if _c6_=3 then call define('_c7_',"style","style=[background=red]");
endcomp;
run;


View solution in original post


All Replies
Trusted Advisor
Posts: 1,934

Re: proc report with color coding

[ Edited ]
Posted in reply to Niugg2010

A partial answer:

 

When you have ACROSS variables in PROC REPORT, you have to refer to the columns of interest by the column number, such as this example, which would color code your column 3:

 

call define("_c3_",'style','style={background=ever60f.}');

Also, note that I have used a format to determine the background color. I don't know if that is necessary in your case or not.

 

Finally, the variable ABOVE is mentioned in your PROC REPORT, but does not exist. Even if you remove the and above>0 from the code, you still get the warning in the SASLOG that FLG is uninitialized. I suspect to use FLG this way, you'd need to move it to the left of the parenthesis in the COLUMN statement, but that screws up the table organization.

 

So I'm not sure you can get this to work, but those are some things to try.

Frequent Contributor
Posts: 90

Re: proc report with color coding

Posted in reply to PaigeMiller

Sorry, the 'above' should be removed. 

Below code still does not work. and in the log it shows "NOTE: Variable flg is uninitialized. " 

why ?

 

If I move the flg varible to the left of the parenthesis, how can I use across to display flag variable?

I can transpose data into several columns, however it will make the code some longer and complicated. 

 

 

proc report data=a NOCOMPLETECOLS;
column trt visno, (param, (flg value ));
define trt / group;
define visno / across;
define param / across;
define flg / display;
compute value;
if flg=1 then call define('_c3_',"style","style=[background=yellow]");
else if flg=2 then call define('_c3_',"style","style=[background=orange]");
else if flg=3 then call define('_c3_',"style","style=[background=red]");
endcomp;
run;

Trusted Advisor
Posts: 1,934

Re: proc report with color coding

[ Edited ]
Posted in reply to Niugg2010

If I move the flg varible to the left of the parenthesis, how can I use across to display flag variable?

 

Probably you can't. You might create a duplicate variable flg1 that has the same values as flg and move that to the left of the parentheses, and use flg1 to determine the colors, but I think that destroys the table organization.

 

As I said, I don't know if you can make this work in PROC REPORT or not.

Frequent Contributor
Posts: 90

Re: proc report with color coding

Posted in reply to PaigeMiller

You are correct. I tried to move the flg into a different place, which resulted a strange output.

Hopefull someone can figure this issue out.

Solution
‎09-27-2017 12:07 PM
Super User
Posts: 10,046

Re: proc report with color coding

Posted in reply to Niugg2010
use option OUT= to check the real variable name you should refer to.




data a;
input TRT $ Visno $ Param $ value flg;
datalines;
TRT1 v1 A 8 1
TRT1 v1 B 6 0
TRT1 v1 C 5 2
TRT1 v2 A 6 0
TRT1 v2 B 7 0
TRT1 v3 C 8 0
TRT2 v1 A 3 1
TRT2 v1 B 7 3
TRT2 v1 C 5 2
TRT2 v2 A 10 0
TRT2 v2 B 6 1
TRT2 v3 C 10 0
TRT3 v1 A 10 2
TRT3 v1 B 16 1
TRT3 v1 C 18 2
TRT3 v2 A 14 1
TRT3 v2 B 11 2
TRT3 v3 C 15 1
;
proc report data=a out=x  nowd NOCOMPLETECOLS;
column trt visno,param,(flg value );
define trt / group;
define visno / across;
define param / across;
define flg / analysis sum;
define value/analysis sum;
compute value;
if _c2_=1  then call define('_c3_',"style","style=[background=yellow]");
else if _c2_=2 then call define('_c3_',"style","style=[background=orange]");
else if _c2_=3 then call define('_c3_',"style","style=[background=red]");

if _c4_=1  then call define('_c5_',"style","style=[background=yellow]");
else if _c4_=2 then call define('_c5_',"style","style=[background=orange]");
else if _c4_=3 then call define('_c5_',"style","style=[background=red]");

if _c6_=1  then call define('_c7_',"style","style=[background=yellow]");
else if _c6_=2 then call define('_c7_',"style","style=[background=orange]");
else if _c6_=3 then call define('_c7_',"style","style=[background=red]");
endcomp;
run;


Frequent Contributor
Posts: 90

Re: proc report with color coding

Thanks. Got it.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 155 views
  • 0 likes
  • 3 in conversation