```Date: Fri, 25 Jul 1997 16:13:42 EDT Reply-To: whitloi1@WESTATPO.WESTAT.COM Sender: "SAS(r) Discussion" From: Ian Whitlock Subject: Re: PROC Report / PROC Tabulate question Comments: To: rainer.hansel.rh@bayer-ag.de Subject: PROC Report / PROC Tabulate question Summary: Two points of view require two separate results merged together. Respondent: Ian whitlock Rainer Hansel 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 ```

Back to: Top of message | Previous page | Main SAS-L page