PROC TABULATE analysis of Select All That Apply, by subgroup (need group total)

[MelissaM]

Am looking for coding guidance (SAS program and output are attached).

While analyzing 'check all that apply' question BY GROUP, the group totals under the boxes are the GRAND TOTALS (repeated 4x) instead of the SUBGROUP TOTALS for each subgroup.

I think the issue is in the data _null_ statement, but am not sure.

I could really use your help.

[ballardw]

Unless your code is extremely long, and yours wasn't it is best to open a text or code box on the forum with the </> or "running man" icon and paste that. So here is your code:

data customer_response;
	input GROUP Customer Factor1-Factor4 Source1-Source3
			Quality1-Quality3;
datalines;
1	1 . . 1 1 1 1 . 1 . .
1	2 1 1 . 1 1 1 . 1 1 .
1	3 . . 1 1 1 1 . . . .
1	4 1 1 . 1 . 1 . . . 1
1	5 . 1 . 1 1 . . . . 1
1	6 . 1 . 1 1 . . . . .
1	7 . 1 . 1 1 . . 1 . .
1	8 1 . . 1 1 1 . 1 1 .
1	9 1 1 . 1 1 . . . . 1
1	10 1 . . 1 1 1 . 1 1 .
1	11 1 1 1 1 . 1 . 1 1 1
1	12 1 1 . 1 1 1 . . . .
1	13 1 1 . 1 . 1 . 1 1 .
1	14 1 1 . 1 1 1 . . . .
1	15 1 1 . 1 . 1 . 1 1 1
1	16 1 . . 1 1 . . 1 . .
1	17 1 1 . 1 1 1 . . 1 .
1	18 1 1 . 1 1 1 1 . . 1
1	19 . 1 . 1 1 1 1 . 1 .
1	20 1 . . 1 1 1 . 1 1 1
1	21 . . . 1 1 1 . 1 . .
1	22 . . . 1 1 1 . 1 1 .
1	23 1 . . 1 . . . . . 1
1	24 . 1 . 1 1 . . 1 . 1
1	25 1 1 . 1 1 . . . 1 1
1	26 1 1 . 1 1 . . 1 . .
1	27 1 . . 1 1 . . . 1 .
1	28 1 1 . 1 . . . 1 1 1
1	29 1 . . 1 1 1 . 1 . 1
1	30 1 . 1 1 1 . . 1 1 .
2	31 . . . 1 1 . . 1 1 .
2	32 1 1 1 1 1 . . 1 1 1
2	33 1 . . 1 1 . . 1 . 1
2	34 . . 1 1 . . . 1 1 .
2	35 1 1 1 1 1 . 1 1 . .
2	36 1 1 1 1 . 1 . 1 . .
2	37 1 1 . 1 . . . 1 . .
2	38 . . . 1 1 1 . 1 . .
2	39 1 1 . 1 1 . . 1 . 1
2	40 1 . . 1 . . 1 1 . 1
2	41 1 . . 1 1 1 1 1 . 1
2	42 1 1 1 1 . . 1 1 . .
2	43 1 . . 1 1 1 . 1 . .
2	44 1 . 1 1 . 1 . 1 . 1
2	45 . . . 1 . . 1 . . 1
2	46 . . . 1 1 . . . 1 .
2	47 1 1 . 1 . . 1 1 . .
2	48 1 . 1 1 1 . 1 1 . .
2	49 . . 1 1 1 1 . 1 . 1
2	50 . 1 . 1 1 . . 1 1 .
2	51 1 . 1 1 1 1 . . . .
2	52 1 1 1 1 1 1 . 1 . .
2	53 . 1 1 1 . 1 . 1 1 1
2	54 1 . . 1 1 . . 1 1 .
2	55 1 1 . 1 1 1 . 1 . .
2	56 1 . . 1 1 . . 1 1 .
2	57 1 1 . 1 1 . 1 . . 1
2	58 . 1 . 1 . 1 . . 1 1
2	59 1 1 1 1 . . 1 1 1 .
2	60 . 1 1 1 1 1 . . 1 1
3	61 1 1 1 1 1 1 . 1 . .
3	62 1 1 . 1 1 . . 1 1 .
3	63 . . . 1 . . . 1 1 1
3	64 1 . . 1 1 1 . 1 . .
3	65 1 . . 1 1 1 . 1 . .
3	66 1 . . 1 1 1 1 1 1 .
3	67 1 1 . 1 1 1 . 1 1 .
3	68 1 1 . 1 1 1 . 1 1 .
3	69 1 1 . 1 1 . 1 . . .
3	70 . . . 1 1 1 . 1 . .
3	71 1 . . 1 1 . 1 . . 1
3	72 1 . 1 1 1 1 . . 1 .
3	73 1 1 . 1 . 1 . 1 1 .
3	74 1 1 1 1 1 1 . 1 . .
3	75 . 1 . 1 1 1 . . 1 .
3	76 1 1 . 1 1 1 . 1 1 1
3	77 . . . 1 1 1 . . . .
3	78 1 1 1 1 1 1 . 1 1 .
3	79 1 . . 1 1 1 . 1 1 .
3	80 1 1 1 1 1 . 1 1 . 1
3	81 1 1 . 1 1 1 1 1 1 .
3	82 . . . 1 1 1 1 . . .
3	83 1 1 . 1 1 1 . 1 1 .
3	84 1 . . 1 1 . . 1 1 .
3	85 . . . 1 . 1 . 1 . .
3	86 1 . . 1 1 1 . 1 1 1
3	87 1 1 . 1 1 1 . 1 . .
3	88 . . . 1 . 1 . . . .
3	89 1 . . 1 . 1 . . 1 1
3	90 1 1 . 1 1 1 . 1 . 1
4	91 . . . 1 1 . . . 1 .
4	92 1 . . 1 1 1 . 1 1 .
4	93 1 . . 1 1 . . 1 1 .
4	94 1 . . 1 1 1 1 1 . .
4	95 1 . . 1 . 1 1 1 1 .
4	96 1 . 1 1 1 1 . . 1 .
4	97 1 1 . 1 1 . . . 1 .
4	98 1 . 1 1 1 1 1 1 . .
4	99 1 1 . 1 1 1 1 1 1 .
4	100 1 . 1 1 1 . . . 1 1
4	101 1 . 1 1 1 1 . . . .
4	102 1 . . 1 1 . 1 1 . .
4	103 1 1 . 1 1 1 . 1 . .
4	104 . . . 1 1 1 . 1 1 1
4	105 1 . 1 1 1 . . 1 . 1
4	106 1 1 1 1 1 1 1 1 1 1
4	107 1 1 1 1 . . . 1 . 1
4	108 1 . . 1 . 1 1 1 . .
4	109 . 1 . 1 1 . . 1 1 .
4	110 1 . . 1 . . . . . .
4	111 1 . . 1 1 1 . 1 1 .
4	112 1 1 . 1 1 1 . . . 1
4	113 1 1 . 1 1 . 1 1 1 .
4	114 1 1 . 1 1 . . . . .
4	115 1 1 . 1 1 . . 1 . .
4	116 . 1 . 1 1 1 1 1 . .
4	117 . 1 . 1 1 1 . . . .
4	118 . 1 1 1 1 . . 1 1 .
4	119 . . . 1 . . . 1 . .
4	120 1 1 . 1 . . . . 1 .
;

data _null_;
   if 0 then set customer_response nobs=count;
   call symput('num',left(put(count,4.)));
   stop;
run;
proc format;
   picture pctfmt low-high='009.9 %';
run;
proc tabulate data=customer_response;
	CLASS GROUP;
   var factor1-factor4 customer;
   table (factor1='Cost'
         factor2='Performance'
         factor3='Reliability'
         factor4='Sales Staff'),
         (n='Count'*f=7. pctn<customer>='Percent'*f=pctfmt9.) ;
   title 'Customer Survey Results: Spring 1996';
   title3 'Factors Influencing the Decision to Buy';
   footnote "Number of Respondents: &num";
run;
options formdlim='' number;




*
The above code was modified from full code available here:
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p1mcul17bwwvfhn19hoojfbxp2oy.htm;

You did not do anything to create create a "count by group" and using a single variable it can only hold one value.

And what value did you expect to appear? All of your groups, at least in the example data shown above, have the same size, 30 records per group. Is that 30 what you expect? If so then look at:

/* clear any existing titles and footnotes*/
title;footnote;
proc tabulate data=customer_response;
	CLASS GROUP;
   var factor1-factor4 customer;
   table group,
         (all='Group count'
         factor1='Cost'
         factor2='Performance'
         factor3='Reliability'
         factor4='Sales Staff'),
         (n='Count'*f=7. pctn<customer>='Percent'*f=pctfmt9.) ;
   title 'Customer Survey Results: Spring 1996';
   title3 'Factors Influencing the Decision to Buy';
run;

The All could be at the bottom after Factor4 if you like.

If you have a first part of the table statement before the row by column that becomes a page dimension so often you can skip BY statements with Proc Tabulate.

To use the macro approach placing a value in a Footnote you would have to create 1) 4 macro variables with the count 2) 4 seperated calls to proc tabulate so the Footnote is with the right table, 3) Have 4 different Footnote statements each using the associated macro variable value 4) Replace BY group or Page dimension with a Where statement to process only one groups values and 5) make that where match the right group for the macro variable in the footnote.

This is not impossible but for general use means getting into full blown macro programming.

Large Economy sized hint: Provide an example of what you actually expect. Probably just providing one of the sub-tables as expected with a clear description of what goes there.

I'm not sure what you are attempting with Formdlim. That option basically does not apply to any ODS destination output except listing

[MelissaM]

@ballardw Appreciate the reply, and suggestions.

Yes, I was seeking SUBGROUP TOTALS (30 for each table).

My preferred output is a footnote, as shown (so a strategically placed ALL in PROC TABULATE would not work).

Am not proficient with call symput() using multiple macros, and gave up after about 2hrs, so forgive (and thank) me for omitting failed attempted code.

Am open to any suggestions (or links to papers showing how to output) subgroup totals at the bottom of the 'check all that apply' tables.

The example posted outputs grand totals below each of the four tables (30+30+30+30=120) instead of the desired 30.

Again, thank you for trying.

[ballardw]

---

@MelissaM wrote:

@ballardw Appreciate the reply, and suggestions.

Yes, I was seeking SUBGROUP TOTALS (30 for each table).

Which I did in the first row of each table.

Why specifically FOOTNOTES? If find having to look for such in a different location than the summary table to be distracting.

What ODS destination do you want/expect to use? Footnotes in some destinations will be at the bottom of a page, not following the table(s) and quite likely would only show the last footnote before the page was created. Or do you want a bunch of small tables each appearing on a different page of a document?

This does what you request:

%macro maketabulate ();
proc sql noprint;
   select group, count(*) into : groupid1-, : groupcount1-
   from  customer_response
   group by group
   ;
quit;
%let Numgroups = &sqlobs;
%do i = 1 %to &numgroups;
   title 'Customer Survey Results: Spring 1996';
   title3 'Factors Influencing the Decision to Buy';
   footnote "Number of Respondents: &&groupcount&i.";
   proc tabulate data=customer_response;
      by group;
   	Where group= &&groupid&i;
      var factor1-factor4 customer;
      table (factor1='Cost'
            factor2='Performance'
            factor3='Reliability'
            factor4='Sales Staff'),
            (n='Count'*f=7. pctn<customer>='Percent'*f=pctfmt9.) ;
   run;

%end;


%mend;

%maketabulate()
title; footnote;

The proc sql generates the different macro variables you needed for the footnote, and optionally one you could use in a matching title statement instead of using "BY" to get a group label.

Caution: use of Indirect macro referencing, those with the && (or more) can be quite difficult to debug and modify with a strong understanding of the macro language.

Note that this can be a very inefficient process if the data set is large because the use of Footnote forces rereading the source data set once for each value of Group variable. 

Also, since you have not stated the ODS destination keep in mind the warning about Footnote and some destinations.