wiki:QueryDataFrame

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>

Last modified 7 months ago Last modified on 11/08/18 10:13:55