Date:         Fri, 30 Sep 2005 11:08:39 -0700
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: Crosstab for large data
Comments: To: jarg@GMX.DE
Content-Type: text/plain; charset="US-ASCII"

Man, I found this way tougher than I first expected.  I really don't
like my 'solution' (not at all confident it will scale to a large dset).
But maybe it'll be useful for giving others something to shoot at. ;-)

data gnu ;
input id  product $ N ;
datalines ;
1     a     1
1     a     1
1     b     1
1     c     1
2     a     1
2     c     1
3     w     1
3     a     1
;
run ;

proc sql ;
   * Count up # of purchases per person/product ;
   * It might be better to use max() rather than sum() here--depends ;
   * on whether you want to differentiate between ppl buying > 1 of ;
   * a given product.  Id try it both ways. ;
   create table people_prods as
   select id, product, sum(n) as n
   from gnu
   group by id, product
   order by id, product
   ;
quit ;

* Transpose to get one row/person w/separate vars for each product ;
proc transpose prefix = prod out = prod ;
   var n ;
   by id ;
   id product ;
run ;

* Change the missing counts to 0s. ;
data prod ;
   set prod ;
   array p{*} prod: ;
   do i = 1 to dim(p) ;
      if p{i} = . then p{i} = 0 ;
   end ;
   drop i ;
run ;

proc print data = prod(obs = 20) ;
run ;

* This is the easiest way I could think of to get all prod*prod
associations ;
proc corr ;
   var prod: ;
run ;


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
jarg@GMX.DE
Sent: Friday, September 30, 2005 10:34 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Crosstab for large data


Hi!

I have a dataset with the following structure:

id  product N
1     a     1
1     a     1
1     b     1
1     c     1
2     a     1
2     c     1
3     w     1
3     a     1


And I would like to get a crosstab that looks like:

   a   b   c  d ...w
a
b
c
d
.    .. COUNT ..
.
.
w

I just need to know how often each product combination was sold .
Unfortunatly the spss crosstab only works with different variables. I
cant do a  crosstab for product by product.

Does anybody have a hint how to solve the problem?

Thanks a lot in advanced!

J