Preloadfmt equivalent in SAS VA (or other solution to keep bars same width)

[FredrikHansson]

Hi.

We're about to rebuild a bunch of sgplot barcharts in VA.

For two reasons it's important for us to always show all the categories(bars) even if the report user have made a subset that do not contain all categories.

* We want it to be obvious for the user to see what categories that get a zero-bar.

* We would like the bars to retain the same width when users view a subset of the data.

I'll first describe how we solve the problem today:

First example of not-desired result. One of the categories is not displayed in the graf.

* First we create a format for the category	;
proc format;
	value age_group
		LOW - 29 = '0-29'
		30 - 39 = '30-39'
		40 - 49 = '40-49'
		50 - 59 = '50-59'
		60 - 69 = '60-69'
		70 - 79 = '70-79'
		80 - 89 = '80-89'
		90 - HIGH = '90-';
quit;

ods graphics / height=250px;

title "One group is missing (0-29)";
PROC SGPLOT DATA=sashelp.heart ;
	format AgeCHDdiag age_group.;
	HBAR AgeCHDdiag / STAT=FREQ;
RUN;

Second example of not-desired result. This time the user have done some subsetting. Bars get super wide.

data work.heart_subset;
	set sashelp.heart;
	where AgeCHDdiag LT 60;
run;
title "Many groups are missing and bars get terribly wide";
PROC SGPLOT DATA=work.heart_subset;
	format AgeCHDdiag age_group.;
	HBAR AgeCHDdiag / STAT=FREQ;
RUN;

Using proc summary with preloadfmt and completeTypes solves our problem.

proc summary data=work.heart_subset completeTypes;
	format AgeCHDdiag age_group.;
	class AgeCHDdiag / preloadfmt ;
	types AgeCHDdiag;
	output out=work.heart_summary;
	label _FREQ_ = "Frequency";
run;

title "All groups are displayed. Bars will always have the same width.";
PROC SGPLOT DATA=work.heart_summary;
	HBARPARM CATEGORY=AgeCHDdiag RESPONSE=_FREQ_;
RUN;

So back to the question. Is there a way to achieve the same result as in the last picture in SAS VA? I have promoted VA internally quite a bit. It will be hard for me to explain to the UX-designers that this can't be done in our shiny new reporting-software. Please help me. 😊

EDIT: I have read Solved: Missing categories in bar chart - SAS Support Communities but I'm kind of hoping there is a better solution today. Inputing zero-data is not a good solution for a report where users can do their own subsetting.

[acordes]

Yes, that's possible.
You'll need the prior data step to create completetypes. The completeness has to be given for any filter drill-down that you grant the users.
It can be achieved with user defined formats. Probably you'll need admin rights to promote therm.
Once you have the udf all set, you promote the table in sas studio assigning to your age this newly created format.
When opening in VA it gets displayed as wished.

Furthermore apply custom sort to this UDF inside VA.

cas mysession dropfmtlib fmtlibname=myFmtLib  /* 2 */
fmtsearchremove;

proc casutil;
deletesource casdata="myFmtLib.sashdat" incaslib="public";
run;

proc format library=work.formats casfmtlib="myFmtLib";
	value age_group
		LOW - 29 = '0-29'
		30 - 39 = '30-39'
		40 - 49 = '40-49'
		50 - 59 = '50-59'
		60 - 69 = '60-69'
		70 - 79 = '70-79'
		80 - 89 = '80-89'
		90 - HIGH = '90-';
quit;

cas mysession savefmtlib fmtlibname=myFmtLib         /* 5 */
   table="myFmtLib.sashdat" caslib=PUBLIC replace;

cas mysession addfmtlib fmtlibname=myFmtLib     /* 2 */
   table="myFmtLib.sashdat" caslib=PUBLIC replacefmtlib;

cas mysession promotefmtlib fmtlibname=myFmtLib REPLACE;

/* go to manage environment and promote he format assuming suoer user role  */

data work.heart_subset;
	set sashelp.heart;
	where AgeCHDdiag LT 60;
frek_dummy=1;
run;

data numbers;
do AgeCHDdiag=25 to 95 by 10;
output;
end;
run;

proc sql;
create table dummy as 
select a.*, b.AgeCHDdiag, 0 as frek_dummy
from work.heart_subset(drop=AgeCHDdiag frek_dummy) a cross join numbers b;
quit;

data public.heart_subset_dummy(promote=yes);
set work.heart_subset(in=a) dummy;
format _AgeCHDdiag age_group_copy.;
_AgeCHDdiag=AgeCHDdiag;
run;

[FredrikHansson]

Thank you for your answer. I realize that solution will work for most reports, but unfortunately not in my case.

What will not work for us is: "The completeness has to be given for any filter drill-down that you grant the users".

• Having to keeping the "completeness-dataset" up to date with the filter parameters is not ideal out of a maintenance point of view.
• The "completeness-dataset" will get very large. There will be around one thousand departments using the report. Each department see only their own data. There will be a date slider where users can select any date interval from the last five years. That makes 1000 * 1,6 million unique combinations. Add to that 5 or 6 other subsetting buttons with each 3 or 4 options...

That is what I had in mind when i wrote "I have read Solved: Missing categories in bar chart - SAS Support Communities but I'm kind of hoping there is a better solution today. Inputing zero-data is not a good solution for a report where users can do their own subsetting.". I realize I could have been (a lot) more specific.

[acordes]

There's another solution. You can use the data driven content which calls a job that executes sas code and outputs the graph back to the report.
Just yesterday 2 real experts in this matter solved me this particular challenge.

[acordes]

I have an idea. Probably you can force all categories to appear independently of the user's choices. This should work using an advanced filter with the object.
And you could only append 7 rows to the original data set each representing one age group. You could assign a filter flag to these few observations. And use this in an OR clause in the advanced filter. Give it a try.

[FredrikHansson]

Thank you. I'll give that a try as soon as we get VA installed.