Including this line in your module.xml file:
<QUERY_APPLICATION_REQUEST_ADD_NON_FILTERED_GROUP_BY_DIMENSION_VALUES_FLAG/>
Causes ibis-view to send a list of all cross-variable dimension values to ibis-q so ibis-q can create a "data frame" – a small dataset named "df_%cross1%%cross2%" that creates a variable named "count" and sets its value to zero for all dimension values. It ensures that all dimension values get included in the results. Without using data_frame with these 2017 meningitis deaths, we would only get 4 counties in the results table, instead of all 33 NM counties:
--------------------------------- 1. TMP - numerator dataset cntyfips _TYPE_ _FREQ_ count . 0 13 13 1 1 9 9 31 1 2 2 39 1 1 1 45 1 1 1 ---------------------------------
You have to call in the ibis-q data_frame file and merge it with the original tmp file (the one shown above). Here is the SAS code in the .def file:
*********************** 2. data_frame **************************; * df_%cross1%%cross2% is a dataset created by IBIS-Q. ; * It consists of %cross1% and %cross2% (if the user specified ; * %cross2%) and a variable named "count" that is set to "0". ; * The results of the proc summary must be merged with the ; * df_%cross1%%cross2% dataset. ; * We don't use the cross1 and cross2 subtotals, anymore, we ; * only use the grand total in the but Kendo table result, but ; * don't delete them because it will mess up the table marginal ; * cell suppression - it will suppress table rows in the table ; * body when it doesn't find the cross1 and cross2 totals. ; ****************************************************************; data frame; set df_%cross1%%cross2%; run; proc sort data=frame; by %cross1% ?cross2? %cross2% ; run; proc print data=frame noobs; title2 '2. Data Frame'; run; data new; merge frame tmp; *must list frame dataset first, then tmp; by %cross1% ?cross2? %cross2% ; run; proc print data=new noobs; title2 '2. NEW, after frame merged with tmp'; run; data tmp; set new; drop _TYPE_ _FREQ_; run;
Here is the df_%cross1%%cross2% data_frame dataset that ibis-q created for for my 2017 meningitis deaths query – it includes all NM counties – all the dimension values, and creates the count variable with a starting value of zero:
--------------------------------- 2. Data Frame count cntyfips 0 . 0 1 0 3 0 5 0 6 0 7 0 9 0 11 0 13 0 15 0 17 0 19 0 21 0 23 0 25 0 27 0 28 0 29 0 31 0 33 0 35 0 37 0 39 0 41 0 43 0 45 0 47 0 49 0 51 0 53 0 55 0 57 0 59 0 61 0 99 ------------------------------
And here is my new tmp dataset after merging the frame with the original tmp dataset:
--------------------------------- 2. TMP count cntyfips 13 . 9 1 0 3 0 5 0 6 0 7 0 9 0 11 0 13 0 15 0 17 0 19 0 21 0 23 0 25 0 27 0 28 0 29 2 31 0 33 0 35 0 37 1 39 0 41 0 43 1 45 0 47 0 49 0 51 0 53 0 55 0 57 0 59 0 61 ---------------------------------
Now my results table includes all counties, even those with zero deaths:
Decedent's County of Residence Deaths per 100,000 Population 95% CI LL 95% CI UL Number of Deaths Number in the Population (person-years) Statistical Stability Bernalillo County 1.3 0.5 2.2 9 679,827 Unstable Catron County 0 0 94.8 0 3,581 Unstable Chaves County 0 0 5.2 0 65,727 Unstable Cibola County 0 0 12.5 0 27,160 Unstable Colfax County 0 0 27.4 0 12,399 Unstable ...
One issue with using the that ADD_NON_FILTERED_GROUP_BY_DIMENSION_VALUES xml element is that the query request URL can get really long. Here is my URL from that example query:
http://127.0.0.1/cgi-bin/hi_iq_func22.exe?config=E:\ibisq\qModules\mort23\_Mort17.cfg&sas=3&test=0&NMRes=1& func=NM_RateCrude100K_data_frame.def&ICDNCHS50=20&Year=2017&cross1=GeoProxy& GeoCnty=1&GeoCnty=3&GeoCnty=5&GeoCnty=6&GeoCnty=7&GeoCnty=9&GeoCnty=11& GeoCnty=13&GeoCnty=15&GeoCnty=17&GeoCnty=19&GeoCnty=21&GeoCnty=23&GeoCnty=25& GeoCnty=27&GeoCnty=28&GeoCnty=29&GeoCnty=31&GeoCnty=33&GeoCnty=35&GeoCnty=37& GeoCnty=39&GeoCnty=41&GeoCnty=43&GeoCnty=45&GeoCnty=47&GeoCnty=49&GeoCnty=51& GeoCnty=53&GeoCnty=55&GeoCnty=57&GeoCnty=59&GeoCnty=61&GeoCnty=99&GeoCnty=.& GeoProxy=GeoCnty
All those "&GeoCnty?=##" pieces are my county dimension values. Sometimes the URL gets too long for a typical request. I guess the default request type is called a "get," which I confess I don’t really understand. But if you use a request type called a "post" there is no character limit on the URL. Thus the second xml element:
<QUERY_APPLICATION_HTTP_REQUEST_TYPE>post</QUERY_APPLICATION_HTTP_REQUEST_TYPE>