The SAS Output Delivery System and reporting techniques

Report with multiple frequencies for the same variable with different formats

Reply
Occasional Contributor
Posts: 5

Report with multiple frequencies for the same variable with different formats

[ Edited ]

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
Super Contributor
Super Contributor
Posts: 266

Re: Report with multiple frequencies for the same variable with different formats

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';

??

Occasional Contributor
Posts: 5

Re: Report with multiple frequencies for the same variable with different formats

[ Edited ]

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.

Super User
Posts: 13,507

Re: Report with multiple frequencies for the same variable with different formats


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.

 

Ask a Question
Discussion stats
  • 3 replies
  • 143 views
  • 0 likes
  • 3 in conversation