PROC SGPANEL: Include Zero Counts for GROUP Variable, Compress Zero Counts for PANELBY Variable

[djbateman]

From my extensive Google searching, I have not been able to find a solution to this problem.  I am wondering if what I want to do is possible in SAS.

I need to create a rather large graph.  I basically have 3 grouping variables.  The first is number of variants (0 or 1).  This variable determines the bars.  The second group is investigator site.  This clusters the bars together.  The third is country.  This is a panelby variable.  Below is the code that I am using (sample data attached).

ods graphics / reset width=10in height=4in;
proc sgpanel data=site;
/*	where country in ('BR','CO'); */
	panelby country / onepanel layout=columnlattice colheaderpos=bottom noborder novarname; 
	vbar siteid / group=nvariants groupdisplay=cluster grouporder=data;
	rowaxis display=(nolabel);
	colaxis display=(nolabel);   
run;

I am running into 2 issues:

1. When a group is not available for the group variable (NVARIANTS), it compresses the empty spaces and centers the bars.  I want the zero space left blank.  Notice how the tick mark for Brazil sites 016 and 018 is inbetween the 2 bars, but for sites 005 and 015, it compresses the empty bar and centers the remaining bar (no space for a zero count bar).  From my understanding, there is no way to make this happen in SAS.
2. The panelby variable (COUNTRY) creates a space for every site even if it's not available in that country.  Notice that Brazil has an empty space for sites 395 and 396.  Also, notice that Colombia is displaying several empty sites (because these are from Brazil and not Colombia).  This is basically what I want to have happen in my first issue, but I don't want it here.  I want to only display sites applicable to the associated country.

Subgroup of my graph:

Basically, I want to swap what is happening in both issues.  I want NVARIANTS to create a spot for each possibilitiy, even if it's not there, but I want it to NOT create a spot for each site unless it is found in that country.

This is what I get on my full data (very cluttered):

But this is what I want the graph to look like (generated manually in Excel, butI have been asked to automate in SAS):

[Ksharp · Accepted solution]

How about this one ?

proc import datafile='c:\temp\BarChartSampleData.xlsx' out=have dbms=xlsx replace;
run;



ods graphics /width=1200px height=600px;
proc sgplot data=have;
where NVARIANTS ne '.';
styleattrs datacolors=(darkred grey);
vbarbasic INVNAME/group=NVARIANTS stat=freq groupdisplay=cluster nooutline  ;
block x=INVNAME block=COUNTRY/nofill labelattrs=(size=40 weight=bold);
xaxis fitpolicy=rotate VALUESROTATE=VERTICAL label='Country/PI';
yaxis grid label='Subject Counts';
keylegend /title='' noborder autooutline;
quit;

[djbateman]

You are a genius.  I don't think I would have figured all this out even after weeks of researching online.  This saved me a ton of time.  I used to be very proficient in SAS graphs, but after I left my first employer 8 years ago, I haven't used graphs much, and I have lost a lot of my skills.  Thank you so much for the refresher course.