Date:         Thu, 9 Nov 2006 21:40:41 -0500
Reply-To:     "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <rdevenezia@WILDBLUE.NET>
Subject:      Re: Frequency Summary (TABULATE OR REPORT) ?
Comments: To: sas-l@uga.edu

Ken Avery wrote:
> Hello,
>
> I'm trying to create a simple frequency summary organized in the
> layout shown below (and using data 'TESTDATA'). With some effort I'm
> sure I can create the table/report with DATA _NULL_.
>
> However, I'd like funnel the data through a procedure (TABULATE OR
> REPORT) using ODS. It seems simple, but I'm drawing a blank. I'd be
> interested in hearing any suggestions that produces the design shown
> below. - Thanks

Proc Report can be coerced to create the report after transposing the
original data.  Detailed understanding of the compute block execution
sequence is needed to grok the sample code.

-----------------------------------
DATA TESTDATA;
infile cards missover;
INPUT SPORTLINE $ PURCHASE_1 PURCHASE_2;
NET = .;
rowid+1;
DATALINES;
RUNNER 13.5 17.25
HIKER 200.75 215.89
RUNNER 22.05 1.01
BIKER 322.55 17.28
UNKNOWN 52.55 29.23
RUNNER 16.34 16.01
HIKER 111.11 117.26
UNKNOWN 51.78 429.11
RUNNER 1.57 399.12
BIKER 456.55 21.01
BIKER 26.55
RUNNER 100.5
UNKNOWN 77.55
HIKER 401.75
RUNNER 522.05 21.01
UNKNOWN 652.55 629.23
BIKER 72.55 73.01
HIKER 11.11 17.26
HIKER 112.11 112.99
HIKER 312.11 52.99
BIKER 19.43 29.77
UNKNOWN 1.44 9.12
BIKER 9.43 669.77
RUN;

PROC FORMAT;
  VALUE BUYRANGE
   .                 = "D. MISSING "
   0     - <  21.010 = "A. < $21.01 "
  21.010 - < 117.260 = "B. $21.01 -< $117.26 "
 117.260 - HIGH      = "C. $117.26 + "
;
RUN;

proc transpose data=testdata out=talldata(rename=(_name_=transaction
col1=amount));
  by rowid sportline;
  var purchase_1 purchase_2 net;
run;

filename report temp;

ods html file=report style=journal;

proc report nowindows data=talldata missing;
  columns sportline transaction amount trigger;
  define sportline / group 'Sport Line';
  define transaction / group 'Transaction' order=data;
  define amount / across format=buyrange. 'Amount Category Counts' ;
  define trigger / display noprint computed;

  compute before transaction;
    iteration_counter = 0;
  endcomp;

  compute trigger;
    iteration_counter + 1;

    if iteration_counter > 1 then return;

    array results r1 r2 r3 r4;
    array counts _c3_ _c4_ _c5_ _c6_;

    select (upcase(transaction));
      when ('PURCHASE_1') do i = 1 to dim(results); results[i] = -counts[i];
end;
      when ('PURCHASE_2') do i = 1 to dim(results); results[i] + counts[i];
end;
      when ('NET')        do i = 1 to dim(results); counts[i] = results[i];
end;
      otherwise;
    end;
  endcomp;

  compute after sportline;
    line ' ';
  endcomp;
run;

ods html close;
-----------------------------------

Richard A. DeVenezia
http://www.devenezia.com/