Date:         Tue, 20 Apr 2004 15:52:46 -0400
Reply-To:     "Lustig, Roger" <roger.lustig@CITIGROUP.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Lustig, Roger" <roger.lustig@CITIGROUP.COM>
Subject:      Re: Difficult Count & Roll-Up SAS problem?
Comments: To: "Nick ." <ni14@MAIL.COM>
Content-Type: text/plain; charset="iso-8859-1"

Nick:

Not difficult at all.

Try this:

%let k=4;

proc format;
invalue prod
A=1
B=2
C=3
D=4
;
run;
data by_person (drop=I J product);
array trans(&k,&k);
do until (last.id);
   set offers;
   by id;

   if first.id then I=input(product,prod.);
   else do;
      j=input(product,prod.);
      trans(I,J)=1;
      I=j;
      end;
   end;
run;

This gives you a row per ID.

For your output, try the following:

proc print data=by_person;
sum trans:;
run;

PROC MEANS (aka PROC SUMMARY) will create a summed-up data set if you need one.

The only thing you still need to do here is make labels for the TRANS variables.  This may involve a macro.

For ntuples, all you need is an n-dimensional array and a few modifications.

Roger

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Nick
.
Sent: Tuesday, April 20, 2004 2:40 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Difficult Count & Roll-Up SAS problem?


Hello SAS-Lers,

A company campaigns three products A, B, and C. A person can receive multiple offers (up to 4 times in a year) of the same product or combinations of products.

For example,

ID     PRODUCT

1      A
1      C

2      B
2      B

3      C
3      A
3      B

4      C
4      A

5      B
5      A
5      C
5      C

I would like to find and the COUNT all tuplet-combinations (PROBLEM # 1) and all triplet-combinations (PROBLEM #2). For the example above, I would like to create all possible columns like this. Below I am showing all 9 possible combinations of the 3 products taken 2 at a time but only one (of the 27 combinations) of the triplets (C->A->A, etc.) Whenever a transition occurs then flag it by a "1" under the appropriate heading. In the example below, ID 1 has received product A followed by C and under the A->C heading I flag it as a 1. Similarly for the rest.

Finally, Once I have all transitions filled in with a 1 or nothing ("0"), then I need to count them. In the example below, the number of observations that have the A->C tarnsition is 1 because I am showing only one observation (ID =1) with that transition. If I had 5 IDs having the A->C transition, then under the column, call it, COUNT_A_C, I would have the number 5, and so on.

Any help on this (difficult?) problem is appreciated. Even though I only have 3 products, it would be nice if the code was general enough to deal with any number of products, be it 3 or 30 or whatever. The cose must also somehow generate the column headings automatically (A->A, A->B->B->C, etc.) for it would be too hard to sit down to figure out all the combinations by hand. This isn't difficult for 3 products but imagine if I had 10 products.

Thanks.

NICK


ID     PRODUCT   A->A A->B A->C B->A B->B B->C C->A C->B C->C     A->A->A A->A->B A->A->C
1      A                    1
1      C

2      B                              1
2      B

3      C
3      A
3      B

4      C                                       1
4      A

5      B
5      A
5      C
5      C


--
___________________________________________________________
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm