Date: Sun, 2 May 2004 16:02:47 -0700
Reply-To: Roger DeAngelis <xlr82sas@AOL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Roger DeAngelis <xlr82sas@AOL.COM>
Organization: http://groups.google.com
Subject: Report vs Tabulate
Content-Type: text/plain; charset=ISO-8859-1
SAS-L rejected this message, too long to attach to previous thread,
so here it is
Soapbox on:
One more comment on the Tab vs Report thread, I do use proc tabulate
to prep data for proc report
proc tabulate data=AevSer out=AevCntPct(keep=AevSev AevRel AevTrm N);
class AevSer AevRel AevTrm;
tables AevRel*AevTrm, (AevSev all)*N*f=5.0 ;
run;
Here Iam only interested in the output table.
Note the triple letter acronyms.
The variables become data.
===========================================================================
I have heard programmers say that Tabulate is the best procedure for
higher dimensional tables ( 3 or more dimensions). I disagree.
consider:
Using Proc Corresp along with SQL and Proc report
gives the programmer much more flexibility.
Adding Ods and Rtf increases the flexibility further.
Tabulate handles cross tabulations of higher dimensional
tables easier than almost any other procedure.
This is often the only reason to use tabulate.
However
Using Proc Corresp along with SQL and Proc report
gives the programmer much more flexibility.
Adding Ods and Rtf increases the flexibility further.
Consider:
TABULATE OUTPUT OF HIGHER DIMENSIONAL TABLE
(partial output for viewing in SAS-l - 3 dimensions)
---------------------------------------------------------------
| | Quarter |
| |--------------------------------------|
| | 1 | 2 |
| |------------+-------------------------|
| | PctN | N | PctN |
|----------------------+------------+------------+------------|
|Country |Product | | | |
|----------|type | | | |
|CANADA |-----------| | | |
| |FURNITURE | 25.00| 48.00| 25.00|
| |-----------+------------+------------+------------|
| |OFFICE | 25.00| 72.00| 25.00|
|----------+-----------+------------+------------+------------|
|GERMANY |FURNITURE | 25.00| 48.00| 25.00|
| |-----------+------------+------------+------------|
| |OFFICE | 25.00| 72.00| 25.00|
|----------+-----------+------------+------------+------------|
|U.S.A. |FURNITURE | 25.00| 48.00| 25.00|
| |-----------+------------+------------+------------|
| |OFFICE | 25.00| 72.00| 25.00|
---------------------------------------------------------------
PROC REPORT OUTPUT OF HIGHER DIMENSIONAL TABLE WITH PREPROCESSING SAME
DATA
-----------------------------------------------------------------------
|
___________________Quarter____________________|
| 1 2 3 4
|
|Country PrdTyp (N=360) (N=360) (N=360) (N=360)
|
|---------------------------------------------------------------------|
|CANADA | FURNITURE| 48(25.0%)| 48(25.0%)| 48(25.0%)|
48(25.0%)|
| |----------+-----------+-----------+-----------+-----------|
| | OFFICE | 72(25.0%)| 72(25.0%)| 72(25.0%)|
72(25.0%)|
|----------+----------+-----------+-----------+-----------+-----------|
|GERMANY | FURNITURE| 48(25.0%)| 48(25.0%)| 48(25.0%)|
48(25.0%)|
| |----------+-----------+-----------+-----------+-----------|
| | OFFICE | 72(25.0%)| 72(25.0%)| 72(25.0%)|
72(25.0%)|
|----------+----------+-----------+-----------+-----------+-----------|
|U.S.A. | FURNITURE| 48(25.0%)| 48(25.0%)| 48(25.0%)|
48(25.0%)|
| |----------+-----------+-----------+-----------+-----------|
| | OFFICE | 72(25.0%)| 72(25.0%)| 72(25.0%)|
72(25.0%)|
-----------------------------------------------------------------------
*/
/*----------------------------------------------------*\
| Code for tabulate - cannot beat the simplicity |
\*----------------------------------------------------*/
PROC TABULATE DATA=sashelp.PrdSale;
CLASS ProdType Quarter Country;
TABLE Country*ProdType, Quarter*(N PCTN<Quarter>);
run;
/*
---------------------------------------------------------------
| | Quarter |
| |--------------------------------------|
| | 3 | 4 |
| |------------+-------------------------|
| | PctN | N | PctN |
|----------------------+------------+------------+------------|
|Country |Product | | | |
|----------|type | | | |
|CANADA |-----------| | | |
| |FURNITURE | 25.00| 48.00| 25.00|
| |-----------+------------+------------+------------|
| |OFFICE | 25.00| 72.00| 25.00|
|----------+-----------+------------+------------+------------|
|GERMANY |FURNITURE | 25.00| 48.00| 25.00|
| |-----------+------------+------------+------------|
| |OFFICE | 25.00| 72.00| 25.00|
|----------+-----------+------------+------------+------------|
|U.S.A. |FURNITURE | 25.00| 48.00| 25.00|
| |-----------+------------+------------+------------|
| |OFFICE | 25.00| 72.00| 25.00|
---------------------------------------------------------------
*/
/*----------------------------------------------------*\
| |
| Although more lines of code this technique is |
| much more flexible |
| |
| Could use ods and tabulate and transpose |
| to do the same thing? |
| |
\*----------------------------------------------------*/
Ods Output Observed=DemNom(Where=(label ne 'Sum') rename=Sum=_6);
Proc Corresp Data=sashelp.prdsale Observed dim=1 missing;
by Country;
Table ProdType, Quarter;
Run;
Ods Output Close;
Ods Select All;
Run;
/*
Output from proc corresp
we could use report and calc percentages in report
I prefer to use sql preprocessing
Obs COUNTRY Label _1 _2 _3 _4 _6
1 CANADA FURNITURE 48 48 48 48 192
2 CANADA OFFICE 72 72 72 72 288
3 GERMANY FURNITURE 48 48 48 48 192
4 GERMANY OFFICE 72 72 72 72 288
5 U.S.A. FURNITURE 48 48 48 48 192
6 U.S.A. OFFICE 72 72 72 72 288
*/
Proc sql;
/* Create Column Labels with (N=XXX) */
Select
Resolve('%Let _'!!Put(Quarter,1.)!!'='
!!Put(Quarter,1.)!!'#(N='!!Put(Count(Quarter),3.)!!');') as Dum
from
sashelp.prdsale
Group
by Quarter;
/* Create Table for report */
Create
Table DemPre as
Select
Country,
Label as PrdTyp,
Put(_1,3.)!!'('!!put(100*_1/ _6,4.1)!!'%)' as _1,
Put(_2,3.)!!'('!!put(100*_2/ _6,4.1)!!'%)' as _2,
Put(_3,3.)!!'('!!put(100*_3/ _6,4.1)!!'%)' as _3,
Put(_4,3.)!!'('!!put(100*_4/ _6,4.1)!!'%)' as _4
From
DemNom
quit;
run;
Proc Report Data=DemPre Nowd Box Split='#';
Cols
Country
PrdTyp
( "_Quarter_"
_1
_2
_3
_4 )
;
define Country/ order;
define PrdTyp /display;
Define _1 / Display "&_1" ;
Define _2 / Display "&_2" ;
Define _3 / Display "&_3" ;
Define _4 / Display "&_4" ;
run;
quit;
/*
-----------------------------------------------------------------------
|
___________________Quarter____________________|
| 1 2 3 4
|
|Country PrdTyp (N=360) (N=360) (N=360) (N=360)
|
|---------------------------------------------------------------------|
|CANADA | FURNITURE| 48(25.0%)| 48(25.0%)| 48(25.0%)|
48(25.0%)|
| |----------+-----------+-----------+-----------+-----------|
| | OFFICE | 72(25.0%)| 72(25.0%)| 72(25.0%)|
72(25.0%)|
|----------+----------+-----------+-----------+-----------+-----------|
|GERMANY | FURNITURE| 48(25.0%)| 48(25.0%)| 48(25.0%)|
48(25.0%)|
| |----------+-----------+-----------+-----------+-----------|
| | OFFICE | 72(25.0%)| 72(25.0%)| 72(25.0%)|
72(25.0%)|
|----------+----------+-----------+-----------+-----------+-----------|
|U.S.A. | FURNITURE| 48(25.0%)| 48(25.0%)| 48(25.0%)|
48(25.0%)|
| |----------+-----------+-----------+-----------+-----------|
| | OFFICE | 72(25.0%)| 72(25.0%)| 72(25.0%)|
72(25.0%)|
-----------------------------------------------------------------------
*/
Soapbox off;
