```Date: Thu, 22 Apr 2004 08:39:44 -0400 Reply-To: Howard Schreier Sender: "SAS(r) Discussion" From: Howard Schreier Subject: Re: Difficult Count & Roll-Up SAS problem? I notice that you do not count, under A->C, the A to C subsequence found within the sequence for ID=5. This simplifies things. Do you really need the matrix as illustrated, or just the final counts? Do you need to enumerate all of the possible sequences, including those not encountered in the data? Assuming, for now, "no" and "no", it's not too complicated. First roll up one observation per ID: data transitions (drop = product); do n = 1 by 1 until (last.id); set example; by id; length transition \$ 100; transition = catx('->',transition,product); end; run; Then count: proc summary data=transitions nway; class n transition; output out=counts(drop = _type_ rename = (_freq_ = count) ); run; Results: Obs n transition count 1 2 A->C 1 2 2 B->B 1 3 2 C->A 1 4 3 C->A->B 1 5 4 B->A->C->C 1 On Tue, 20 Apr 2004 13:40:06 -0500, Nick . wrote: >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 ```

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