The SAS Output Delivery System and reporting techniques

How to Colour Columns as Data Bars and by Using Conditional Formatting

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

How to Colour Columns as Data Bars and by Using Conditional Formatting

Hello everyone,

 

I'm trying to make "Data Bars" and "Conditional Fromatting" in SAS, which we can do in Excel. I have a similar image which is created in Excel as follows. Excel helps us by the following steps -> Style->Conditional Formatting ->Data Bars.  I prepared a sample data. If it is possible, I would like to see last two columns the image as below.

 

Data Have;
Length Characteristics $ 20 Dev 8 Model 8 Val 8 ModelDev 8 DevVal 8 ;
Infile Datalines Missover;
Input Characteristics Dev Model Val ModelDev DevVal;
Datalines;
WOE_variable_1 0.1 0.1 0.1 0.1 0.7
WOE_variable_2 0.1 0.1 0.1 0.1 0.3
WOE_variable_3 0.1 0.1 0.1 -0.1 0.1
WOE_variable_4 0.1 0.1 0.1 0.1 0.5
WOE_variable_5 0.1 0.1 0.1 0.1 0.1
;
RUN;
PROC FORMAT;
picture pctpic (round) low-<0 ='009.99%' (prefix='-' mult=10000)
0-high='009.99%' (mult=10000)
;
RUN;


PROC REPORT Data=Have;

Column Characteristics ("IV" Dev Model Val) ModelDev DevVal;

Define Characteristics / Display "Characteristics (Variables)";
Define Dev / Display "Dev" STYLE(column)={backgroundcolor=Yellow};
Define Model / Display "Model" STYLE(column)={backgroundcolor=Yellow};
Define Val / Display "Val" STYLE(column)={backgroundcolor=Yellow};
Define ModelDev / Display "Difference of IV / Model - Dev" Format= pctpic.;
Define DevVal / Display "Difference of IV / Dev - Val" Format= Percent7.2;

/*Compute ModelDev;*/
/*Call Define(_col_,'style',*/

Run;

Report.png

 

Thank you,


Accepted Solutions
Solution
‎04-16-2016 09:02 AM
SAS Super FREQ
Posts: 676

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

Hi

 

You will need to create a graph for each school district. An then include those graphs in the Proc REPORT

 

See the example below, it just creates the one graph which is used for all the schools.

 

ods _all_ close;

Data Ranks;
  Length RateRank 8  SchDist $ 20 VarCount 8 VarRate 8;
  Infile Datalines Missover;
  Input RateRank SchDist VarCount VarRate;
  Datalines;
1 Auburn 523 720
2 Seattle 3425 560
3 Higline 662 543
4 SouthCentral 90 539
5 Kent 785 524
6 Renton 429 407
7 Shoreline 225 334
8 FederalWay 424 338
9 Tahoma 69 198
10 SnoqualmieValley 61 183
;
Run;

*
* load all the SAS/GRAPH macros
*;
%annomac


Data Anno;
*
* declare the variables used by the annotate macros
*;
%dclanno

*
* set the coordinate system, in our case % of graphics area
*;
xsys="3";
ysys="3";
*
* set how to measure size for text (pt)
*;
hsys="D";
*
* use macros to create two bars and a text
*;
%Bar(0,0,100,100,cx0000ff,0,e);
%Bar(0,0,75,100,cxff0000,0,s);
%LABEL(100, 50, "75", cx000000, 0, 0, 8, none , <); 

Run;

ods listing;
OPTIONS printerpath=postscript;

filename bar 'c:\temp\barsample.png';
goptions reset=all;
goptions
  device=png gsfname=bar
  xpixels=100 ypixels=25;
;

Proc gslide Anno=Anno;
Run;

Quit;

ods listing close;


Proc Format;
  Value ColorList
    1-3="cxff0000"
    4-6="cxff9999"
    7-10="cxffeeb8"
    11-13="cx9999ff"
    14-16="cx0000ff"
    17-19="white"
  ;
  Value TextColor
    1,2,3='white'
    other='black'
  ;
RUN;

ods html file="c:\temp\sample.html";

PROC REPORT data=Ranks nowd;
  Columns RateRank SchDist
    ("Selected Measure" VarCount VarRate);
  Define RateRank / "Rank" group style=[background=ColorList. foreground=TextColor.];
  Define SchDist / "School District";
  Define VarCount / "Count";
  Define VarRate / "Rate";
RUN;


PROC REPORT data=Ranks nowd;
  Columns RateRank SchDist
    ("Selected Measure" VarCount VarRate)
    showBar
  ;
  Define RateRank / "Rank" group;
  Define SchDist / group "School District";
  Define VarCount / "Count";
  Define VarRate / "Rate";
  Define ShowBar / "Relative Rate" computed;

  compute ShowBar / char;
    call define(_col_,'style',"style=[preimage='c:\temp\barsample.png']");
  endcomp;
Run;
ods html close;

Bruno

View solution in original post


All Replies
Grand Advisor
Posts: 9,593

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

I think you need PROC SGPLOT to get a image ,NOT a excel file .

OR you need call define() + style={preimage=....} , but that is very hard to code and need many image to display it .

Super Contributor
Posts: 381

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

[ Edited ]

Hello Xia,

 

Actually, I was trying to ask that how to show horizontal bar in column's cells while we use PROC REPORT procedure or by the help of any other procedures. I mean I don't want to add a image into cells. I think I can create a report by using PROC SGPLOT as below but how can I show it in a table, into cell ? Are there any possibilities ? I just wanted to indicate that to make clear. If it is still not possible I can understand .

 

Farklı Kaydet.png

 

Thank you

Grand Advisor
Posts: 9,593

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

Oh. As far as I know, that is almost impossible . Maybe Cynthia have some idea . Calling Cynthia .......

SAS Super FREQ
Posts: 676

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

Hi

 

Have a look at this paper http://support.sas.com/resources/papers/proceedings09/221-2009.pdf

It shows how to create the graphs first, and then add it to your report.

 

Bruno

 

Super Contributor
Posts: 381

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

Hello Bruno,

 

Thank you for trying to help me, I checked the Web Site link. Actually, I'm little bir confused in Data Anno part. I wrote the datas in my enivorenment but I couldn't exactly get it. Can anybody help me for the following codes ?

 

Data Ranks;
Length RateRank 8  SchDist $ 20 VarCount 8 VarRate 8;
Infile Datalines Missover;
Input RateRank SchDist VarCount VarRate;
Datalines;
1 Auburn 523 720
2 Seattle 3425 560
3 Higline 662 543
4 SouthCentral 90 539
5 Kent 785 524
6 Renton 429 407
7 Shoreline 225 334
8 FederalWay 424 338
9 Tahoma 69 198
10 SnoqualmieValley 61 183
;
Run;

PROC REPORT data=Ranks nowd;
	Columns RateRank SchDist
	("Selected Measure" VarCount VarRate);
Define RateRank / "Rank" group style=[background=ColorList. foreground=TextColor.];
Define SchDist / "School District";
Define VarCount / "Count";
Define VarRate / "Rate";
RUN;

Proc Format;
	Value ColorList
	1-3="cxff0000"
	4-6="cxff9999"
	7-10="cxffeeb8"
	11-13="cx9999ff"
	14-16="cx0000ff"
	17-19="white";
	Value TextColor
	1,2,3='white'
	other='black';
RUN;

/*
Data Anno;
%Bar(0,0,100,100,black,0,e);
%Bar(0,10,75,90,cxff0000,0,s);
%Bar(75,50,0,360,6,black,s,3);
Run;

filename Bar "...\Highline.gif";
goptions device=gif gsfname=bar hsize=1.5in vsize=.2in;

Proc gslide Anno=Anno;
Run;
Quit;*/

PROC REPORT data=Ranks nowd;
	Columns RateRank SchDist
	("Selected Measure" VarCount VarRate);
Define RateRank / "Rank" group ;
Define SchDist / group "School District";
Define VarCount / "Count";
Define VarRate / "Rate";
/*Define ShowBar / "Relative Rate" computed;

compute ShowBar / char;
	call define(_col_,'style',"style=[preimage='"||SchDist||".gif']");
endcomp;*/
Run;

Thank you

Solution
‎04-16-2016 09:02 AM
SAS Super FREQ
Posts: 676

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

Hi

 

You will need to create a graph for each school district. An then include those graphs in the Proc REPORT

 

See the example below, it just creates the one graph which is used for all the schools.

 

ods _all_ close;

Data Ranks;
  Length RateRank 8  SchDist $ 20 VarCount 8 VarRate 8;
  Infile Datalines Missover;
  Input RateRank SchDist VarCount VarRate;
  Datalines;
1 Auburn 523 720
2 Seattle 3425 560
3 Higline 662 543
4 SouthCentral 90 539
5 Kent 785 524
6 Renton 429 407
7 Shoreline 225 334
8 FederalWay 424 338
9 Tahoma 69 198
10 SnoqualmieValley 61 183
;
Run;

*
* load all the SAS/GRAPH macros
*;
%annomac


Data Anno;
*
* declare the variables used by the annotate macros
*;
%dclanno

*
* set the coordinate system, in our case % of graphics area
*;
xsys="3";
ysys="3";
*
* set how to measure size for text (pt)
*;
hsys="D";
*
* use macros to create two bars and a text
*;
%Bar(0,0,100,100,cx0000ff,0,e);
%Bar(0,0,75,100,cxff0000,0,s);
%LABEL(100, 50, "75", cx000000, 0, 0, 8, none , <); 

Run;

ods listing;
OPTIONS printerpath=postscript;

filename bar 'c:\temp\barsample.png';
goptions reset=all;
goptions
  device=png gsfname=bar
  xpixels=100 ypixels=25;
;

Proc gslide Anno=Anno;
Run;

Quit;

ods listing close;


Proc Format;
  Value ColorList
    1-3="cxff0000"
    4-6="cxff9999"
    7-10="cxffeeb8"
    11-13="cx9999ff"
    14-16="cx0000ff"
    17-19="white"
  ;
  Value TextColor
    1,2,3='white'
    other='black'
  ;
RUN;

ods html file="c:\temp\sample.html";

PROC REPORT data=Ranks nowd;
  Columns RateRank SchDist
    ("Selected Measure" VarCount VarRate);
  Define RateRank / "Rank" group style=[background=ColorList. foreground=TextColor.];
  Define SchDist / "School District";
  Define VarCount / "Count";
  Define VarRate / "Rate";
RUN;


PROC REPORT data=Ranks nowd;
  Columns RateRank SchDist
    ("Selected Measure" VarCount VarRate)
    showBar
  ;
  Define RateRank / "Rank" group;
  Define SchDist / group "School District";
  Define VarCount / "Count";
  Define VarRate / "Rate";
  Define ShowBar / "Relative Rate" computed;

  compute ShowBar / char;
    call define(_col_,'style',"style=[preimage='c:\temp\barsample.png']");
  endcomp;
Run;
ods html close;

Bruno

Super Contributor
Posts: 381

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

Bruno,

 

Thank you very much. Can you share with me the barsample.png, please ?

 

Thank you,

Super Contributor
Posts: 381

Re: How to Colour Columns as Data Bars and by Using Conditional Formatting

 

Bruno,

 

I would like to apologise to you. I was very busy today. Now, I checked your code again in more detailed and I understood better. Thank you very much. I'm going to try some addtional things on your code. If I can do it I'll accept your answer.

 

Thanks a lot again.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 785 views
  • 2 likes
  • 3 in conversation