Date: Fri, 25 Jul 1997 16:13:42 EDT
Reply-To: whitloi1@WESTATPO.WESTAT.COM
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Ian Whitlock <whitloi1@WESTATPO.WESTAT.COM>
Subject: Re: PROC Report / PROC Tabulate question
Subject: PROC Report / PROC Tabulate question
Summary: Two points of view require two separate results merged
together.
Respondent: Ian whitlock <whitloi1@westat.com>
Rainer Hansel <rainer.hansel.rh@bayer-ag.de> asked an interesting
question about tabulating data. He wants a report like
> ____Mail system_____ Total Total
> Country CC:Mail Lotus Notes primary secondary
> ---------------------------------------------------
> France 27 12 39 8
> Germany 0 111 111 14
> Italy 0 25 25 3
> USA 124 18 142 22
> -Total- 151 166 317 47
The problem here is the underlying data. I assume each record gives a
country(1-4), mail system(1,2), and primary(1)/secondary(0) indication.
He wants the primary counts split out by system, but not the secondary
counts. This is what makes the problem interesting. Neither PROC
REPORT nor PROC TABULATE can handle this dual point of view in one
report.
The answer is to prepare the data with two PROC SUMMARYs, one for each
point of view. Then merge the results by country and finally use PROC
TABULATE to format the report. Here is the code together with test
data.
data w ;
input country system prime @@ ;
if prime = 0 then
do
system = . ;
sec = 1 ;
end ;
cards ;
1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 2 1 1 1 0
2 1 1 2 1 1 2 1 1 2 1 1 2 2 1 2 2 1 2 1 0
3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 2 1 3 2 1 3 1 0 3 2 0
run ;
/* primary summary distinguishing system */
proc summary data = w nway ;
where system ^= . ;
class country system ;
var prime ;
output out = w1 ( keep = country system prime )
sum = ;
run ;
/* secondary distinction wiping out the distinction */
proc summary data = w nway ;
where system = . ;
class country ;
var sec ;
output out = w2 ( keep = country sec )
sum = ;
run ;
data m ;
merge w1 w2 ;
by country ;
if not first.country then /* w1 may have multiple records */
sec = . ; /* per by group */
run ;
proc tabulate data = m ;
class country system ;
var prime sec ;
table country all ,
system*prime=' '*sum=' ' prime*sum=' ' sec*sum=' ' ;
run ;
Ian Whitlock
