Howard,

Thanks; this also is a propogate solution, as Ted proposes.  Yes, we do have to present the results of the subsets usually without its complement on most of the reports that we do this type of thing on (and we do this very often which is why I'm looking for a better solution for these).

-Mary

--- hs@howles.com wrote:

From: Howard Schreier <hs@howles.com>
To: Mary <mlhoward@AVALON.NET>
Subject: Re: Creating one report with columns based on different subsets of the data?
Date: Wed, 30 Dec 2009 22:17:04 -0500

This is a natural for PROC TABULATE. If you wanted a block for non-
switchers it would be pretty simple. Calling for the switchers block
without its complement makes it a bit tricky.

One approach is to propagate observations:

data fortabul ;
set all_data ;
length Scope $ 15 ;
scope = 'All Surveys' ;
output ;
if switch_to EQ upcase(switch_to) ;
scope = 'Switchers Only' ;
output ;
run ;

Then

proc format ;
value new 0 = 'Existing' 1 = 'New' ;
run ;

proc tabulate data=fortabul ;
class switch_to scope new ;
format new new. ;
table switch_to all
       ,
       scope=' ' * ( new=' ' all ) * ( n * f=5. pctn<switch_to all> *
f=5.1 )
       /
       rts=15
          ;
run ;

I'll leave the presentation details (esp. ordering of rows and
columns) to you.

Your existing code can be shrunk. There's no need to close and open
PROC SQL between statements. One SQL session can have multiple
statements. There are two series of almost identical DATA steps which
derive different subsets. Each series can be rolled up into one step
with a SELECT tree to control the output.