Horizontal bar chart with table

[sarahsasuser]

I'd like to create something like the chart below, where there is a table with data on the left and something like a horizontal bar chart on the right. The boxes represent days and each stage of the building process is charted. For instance, building 1201 had the longest Stage 1 time (of 10 days) and Permitting took 5 days at building 2215.

Building #

Address

Data

Type

Time to Build – Length of Time (in Days)

1201

1111 Water Street

TS

Office

1578

12 Spring Street

HV

Residential

1785

1526 Fern Avenue

TS

Office

2215

2900 Wayne Street

TF

Office

	Negotiation
	Stage 1
	Stage 2
	Permits

I looked online and Robert Allison's horizontal bar chart comes the closest: http://robslink.com/SAS/democd32/survey.htm

I just need to add 3 columns of data to the left. Any suggestions?

Or, could I use proc tabulate or proc report to shade in the length of time data?

[Cynthia_sas · Accepted solution]

Hi:

  I'm sure there are other ways to do this, such as the example you posted or with ODS Graphics. However, if you just wanted something that would work without making an image, as colored boxes, my tendency is to use PROC REPORT. I made some fake data, not knowing what your structure was, I just made the simplest structure I could think of. A user-defined format is assigned to each possible value for DAY. I made up some codes for the 4 categories you use for colors. I realize that your data could be more complex than this, but I didn't have much time to devote to more complicated data. Screenshot attached.

cynthia

** make some data and make some of the days have missing values;
** so that you can see how TOTDAYS changes on every obs in the report;
data project;
  length Address $30 Code $2 Type $12 Day1-Day12 $3;
  infile datalines dlm=',' dsd;
  input Building_No
        Address $
        Code $
        Type $
        (Day1-Day12) ($);
 
return;
datalines;
1201,"1111 Water Street",TS,Office,s1,s1,s1,s1,s1,s1,s1,s1,s1,s1,s2,s2
1578,"12 Spring Street",HV,Residential,s1,s1,s1,s1,s1,s1,s1,s1,s2,s2,s2,s2
1785,"1526 Fern Avenue",TS,Office,ngt,ngt,ngt,ngt,ngt,ngt,s1,s1,s2,s2,,
2215,"2900 Wayne Street",TF,Office,s1,s1,s1,pm,pm,pm,pm,pm,s2,,,
;
run;
  
ods listing;
proc print data=project;
run;
  
proc format;
  value $colr 's1'='cx666666'
              's2'='cxcccccc'
              'ngt' = 'black'
              'pm'='cyan'
             other='white';
run;

ods listing close;
ods html file='c:\temp\hilite_cells.html' style=rtf;
ods escapechar='~';
    
proc report data=project nowd split='*'
  style(header)={background=white};
  column building_no address  code  type 
        ("Time to Build*Length of Time in Days" day1-day12) totdays;
  define building_no / display;
  define address / display;
  define code / display 'Data';
  define type / display;
  define day: / display
         style(column)={background=$colr. font_size=6pt color=$colr. width=.25in};
  define totdays / computed 'Total Days';
  compute totdays;
     totdays = 12 - cmiss(of day1-day12);
  endcomp;
  compute after / style={just=l};
    length colorkey $50;
    colorkey = "Colorkey:";
    line colorkey $50.;
    line '~{style[background=black color=black]...............}'
         '~{style[background=white color=black] Negotiation}';
   
    line '~{style[background=cx666666 color=cx666666]...............}'
         '~{style[background=white color=black] Stage 1}';
  
    line '~{style[background=cxcccccc color=cxcccccc]...............}'
         '~{style[background=white color=black] Stage 2}';
  
    line '~{style[background=cyan color=cyan]...............}'
         '~{style[background=white] Permits}';
  endcomp;
run;
ods html close;
title;

[sarahsasuser]

Thanks Cynthia. Is there any way to merge the cells labeled Day1-Day12. I'd either like to keep those cells blank or label it "Length of time in days." thanks!