Report with multiple frequencies for the same variable with different formats

[AYBiBTU]

I have the example code:

data have;
input foo @@;
cards;
1 2 3 4 5 1 3 1 3 5 5 5
;
run;

proc format;
    value wide (default=20)
	    1     = 'VERY SATISFIED'
		2     = 'SATISFIED'
		3     = 'DISSATISFIED'
		4     = 'VERY DISSATISFIED'
		other = 'NOT APPLICABLE';

    value thin (default=20)
	    1, 2  = 'SATISFIED'
		3, 4  = 'DISSATISFIED'
		other = 'NOT APPLICABLE';
run;

options missing=' ';
proc report data=have nowd spanrows out=foo;
columns foo=ord foo=cnt foo=bar(n) foo=baz(n);
define ord / group order=internal;
define cnt / analysis n noprint;
define bar / group format=wide.;
define baz / group format=thin.;
run;

Which produces output like

foo	foo	n	foo	n
1	VERY SATISFIED	3	SATISFIED	3
2	SATISFIED	1	SATISFIED	1
3	DISSATISFIED	3	DISSATISFIED	3
4	VERY DISSATISFIED	1	DISSATISFIED	1
5	NOT APPLICABLE	4	NOT APPLICABLE	4

How instead to I get results like the following?

foo	foo	n	foo	n
1	VERY SATISFIED	3	SATISFIED	4
2	SATISFIED	1
3	DISSATISFIED	3	DISSATISFIED	4
4	VERY DISSATISFIED	1
5	NOT APPLICABLE	4	NOT APPLICABLE	4

[HB]

What happens if you cheat it and try to duck the issue with:

    value thin (default=20)
	        1     = 'VERY SATISFIED'
		2     = ' '
		3     = 'DISSATISFIED'
		4     = ' '
		other = 'NOT APPLICABLE';

??

[AYBiBTU]

That will not work in my real situation as I am using pre-compiled formats from a external format library.  It also would not have the desired result for the final frequency column.

[ballardw]

---

@AYBiBTU wrote:

That will not work in my real situation as I am using pre-compiled formats from a external format library.  It also would not have the desired result for the final frequency column.

---

I suspect that you are going to have issues with most approaches as long as your formats use other='NOT APPLICABLE' as then missing values will have 'NOT APPLICABLE' regardless of the setting of the missing option.

This creates a table that looks like your desired output for content though not column headings and does require creating additional variables.

data have;
input foo @@;
cards;
1 2 3 4 5 1 3 1 3 5 5 5
;
run;
proc format LIBRARY=WORK;
    value wide (default=20)
	   1     = 'VERY SATISFIED'
		2     = 'SATISFIED'
		3     = 'DISSATISFIED'
		4     = 'VERY DISSATISFIED'
		5-high = 'NOT APPLICABLE';

    value thin (default=20)
	   1, 2  = 'SATISFIED'
		3, 4  = 'DISSATISFIED'
		5-high = 'NOT APPLICABLE';
run;
proc summary data=have nway;
   class foo;
   var foo;
   format foo wide.;
   output out=sum1 (drop= _: ) n=foon;
run;
proc summary data=have nway;
   class foo;
   var foo;
   format foo thin.;
   output out=sum2(drop= _: ) n=foon2;
run;
options missing=' ';
proc sql;
   create table want as
   select a.foo format=f1., put(a.foo,wide.) as foow,foon,put(b.foo,thin.) as foot,b.foon2
   from sum1 as a
        left join
        sum2 as b
        on a.foo = b.foo
  ;
quit;

So you may want to discuss the use of "other" with the external format library manager.