Hello friends,
I want to use proc report.
I wan to color the dif columns based on same cell value.
Here It was done 100% well.
If dif_P10>0.05 then I want to color dif_P10 in red
If dif_P20>0.05 then I want to color dif_P20 in red
If dif_P30>0.05 then I want to color dif_P30 in red
I also want to color cells based on other cells values.
If dif_P10>0.05 then I want to color P10 in red
If dif_P20>0.05 then I want to color P20 in red
If dif_P30>0.05 then I want to color P30 in red
and so on
Here I dont know how to do it.
Can anyone help please?
I tried use code with column location but it didnt work
data have;
input P_IND_Prob mon P10 P20 P30 P40 P50 P60 P70 P80 P90;
cards;
0 202507 0.1 0.15 0.25 0.31 0.49 0.46 0.6 0.75 0.8 0.97
0 202506 0.08 0.13 0.14 0.44 0.52 0.65 0.74 0.82 0.91 0.94
;
run;
proc sort data=have;
by mon;
run;
data have2;
set have;
by mon;
dif_p10 =abs(p10-lag(p10));
dif_p20 =abs(p20-lag(p20));
dif_p30 =abs(p30-lag(p30));
dif_p40 =abs(p40-lag(p40));
dif_p50 =abs(p50-lag(p50));
dif_p60 =abs(p60-lag(p60));
dif_p70 =abs(p70-lag(p70));
dif_p80 =abs(p80-lag(p80));
dif_p90 =abs(p90-lag(p90));
run;
Proc format ;
value highlight_S4_Fmt
0.05<-high = 'red'
;
Run;
proc report data=have2 missing nowd style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ;
column
mon
P10
P20
P30
P40
P50
P60
P70
P80
P90
dif_p10
dif_p20
dif_p30
dif_p40
dif_p50
dif_p60
dif_p70
dif_p80
dif_p90
;
define mon/DISPLAY ;
define P10/DISPLAY f=percent10.4 ;
define P20/DISPLAY f=percent10.4;
define P30/DISPLAY f=percent10.4;
define P40/DISPLAY f=percent10.4;
define P50/DISPLAY f=percent10.4;
define P60/DISPLAY f=percent10.4;
define P70/DISPLAY f=percent10.4;
define P80/DISPLAY f=percent10.4;
define P90/DISPLAY f=percent10.4;
define dif_p10/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p20/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p30/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p40/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p50/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p60/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p70/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p80/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p90/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
Run;
tried this code but it didnt work!
Dif columsn are not colored based on the criteria
proc report data=have2 missing nowd style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ;
column
mon
P10
P20
P30
P40
P50
P60
P70
P80
P90
dif_p10
dif_p20
dif_p30
dif_p40
dif_p50
dif_p60
dif_p70
dif_p80
dif_p90
;
define mon/DISPLAY ;
define P10/DISPLAY f=percent10.4 ;
define P20/DISPLAY f=percent10.4;
define P30/DISPLAY f=percent10.4;
define P40/DISPLAY f=percent10.4;
define P50/DISPLAY f=percent10.4;
define P60/DISPLAY f=percent10.4;
define P70/DISPLAY f=percent10.4;
define P80/DISPLAY f=percent10.4;
define P90/DISPLAY f=percent10.4;
define dif_p10/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p20/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p30/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p40/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p50/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p60/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p70/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p80/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
define dif_p90/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.];
compute P10;
if dif_p10>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P20;
if dif_p20>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P30;
if dif_p30>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P40;
if dif_p40>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P50;
if dif_p50>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P60;
if dif_p60>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P70;
if dif_p70>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P80;
if dif_p80>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
compute P90;
if dif_p90>0.05 then call define (_col_,"style", "style={background=red}");
endcomp;
Run;
PROC REPORT scan table from left to right.
So when you want to refer to left column base on right column, you need use right column within COMPUTE block.
data have; input P_IND_Prob mon P10 P20 P30 P40 P50 P60 P70 P80 P90; cards; 0 202507 0.1 0.15 0.25 0.31 0.49 0.46 0.6 0.75 0.8 0.97 0 202506 0.08 0.13 0.14 0.44 0.52 0.65 0.74 0.82 0.91 0.94 ; run; proc sort data=have; by mon; run; data have2; set have; by mon; dif_p10 =abs(p10-lag(p10)); dif_p20 =abs(p20-lag(p20)); dif_p30 =abs(p30-lag(p30)); dif_p40 =abs(p40-lag(p40)); dif_p50 =abs(p50-lag(p50)); dif_p60 =abs(p60-lag(p60)); dif_p70 =abs(p70-lag(p70)); dif_p80 =abs(p80-lag(p80)); dif_p90 =abs(p90-lag(p90)); run; Proc format ; value highlight_S4_Fmt 0.05<-high = 'red' ; Run; proc report data=have2 missing nowd style(report)={frame=box font_size=8pt bordercolor=black borderwidth=2px} ; column mon P10 P20 P30 P40 P50 P60 P70 P80 P90 dif_p10 dif_p20 dif_p30 dif_p40 dif_p50 dif_p60 dif_p70 dif_p80 dif_p90 ; define mon/DISPLAY ; define P10/DISPLAY f=percent10.4 ; define P20/DISPLAY f=percent10.4; define P30/DISPLAY f=percent10.4; define P40/DISPLAY f=percent10.4; define P50/DISPLAY f=percent10.4; define P60/DISPLAY f=percent10.4; define P70/DISPLAY f=percent10.4; define P80/DISPLAY f=percent10.4; define P90/DISPLAY f=percent10.4; define dif_p10/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p20/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p30/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p40/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p50/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p60/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p70/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p80/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; define dif_p90/DISPLAY f=percent10.4 style=[background=highlight_S4_Fmt.]; compute dif_p90; if dif_p10>0.05 then call define ('P10',"style", "style={background=red}"); if dif_p20>0.05 then call define ('P20',"style", "style={background=red}"); if dif_p30>0.05 then call define ('P30',"style", "style={background=red}"); if dif_p40>0.05 then call define ('P40',"style", "style={background=red}"); if dif_p50>0.05 then call define ('P50',"style", "style={background=red}"); if dif_p60>0.05 then call define ('P60',"style", "style={background=red}"); if dif_p70>0.05 then call define ('P70',"style", "style={background=red}"); if dif_p80>0.05 then call define ('P80',"style", "style={background=red}"); if dif_p90>0.05 then call define ('P90',"style", "style={background=red}"); endcomp; Run;
As I understand you wrote
compute dif_p90;
but you can type here any variable that appear in right of all dif variables .
I didnt see any information to read about this rule -when you want to refer to left column base on right column, you need use right column within COMPUTE block
And If there is no any variable on the right?
For example:
If variables order is - IND MON dif_P10 dif_ P20 dif_P30 P10 P20 P30
And I want to -
color P10 bases on dif_p10
color P20 bases on dif_p20
color P30 bases on dif_p30
But as you can see-
dif_p10 is left to P10
dif_p20 is left to P20
dif_P30 is left to p30
I see that the rule is true but I want to read a bit about it
The following is in the documentation under Required arguments:
report-item
specifies a data set variable, a computed variable, or a statistic to associate the compute block with. You must include the report item in the COLUMN statement. If the item is a computed variable, then you must include a DEFINE statement for it.
Note The position of a computed variable is important. PROC REPORT assigns values to the columns in a row of a report from left to right. Consequently, you cannot base the calculation of a computed variable on any variable that appears to its right in the report.
I would also suggest the following paper:
https://support.sas.com/resources/papers/proceedings15/SAS1642-2015.pdf
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.