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.
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;
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".
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.