Date:         Tue, 19 Sep 2006 12:25:18 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Datamanagement: Aggregate Information Linewise to account
              subjects to groups

On Fri, 15 Sep 2006 02:46:37 -0700, Dubro <dubro@DOLIC.DE> wrote:

>Dear Group,
>
>I have a file with following structure:
>
>userid partner method
>1       1         A
>1       3         B
>1       4         F
>2       3         B
>3       1         A
>3       4         C
>
>
>Now I would like to allocate every user (userid) to a group. The group
>depends on the values in variables partner and method (e.g. Group 1 is
>when partner = 1 and method = A).
>
>I tried several approaches as using proc transpose and so on. Still I
>haven't found the optimal strategy to solve this problem. I'm thinking
>of something using retain. Maybe any one out there had a similar
>problem and could give me a hint?
>
>Thank all of you.
>
>Dubravko Dolic
>www.komdat.com

I'm still sort of guessing about the details. Here's one interpretation.

Here's the table Dubro provided:

   data users;
   input
userid partner method $; cards;
1       1         A
1       3         B
1       4         F
2       3         B
3       1         A
3       4         C
   ;

Think of each PARTNER/METHOD pair as a credential held by a particular user.

Presumably there is a second table:

   data groups;
   input group $ partner method $;
   cards;
   one 1 A
   one 3 B
   two 1 A
   two 4 F
   six 3 B
   six 4 C
   six 5 F
   ;

Think of each PARTNER/METHOD pair as a credential required for entry to a
particular group. A user must have all of the credentials (ie, they are to
be ANDed, not ORed).

Here's an SQL solution.

   proc sql;

First clone the users' credential for each group. Think of each
PARTNER/METHOD pair as a credential:

   create view have as
    select distinct users.*, groups.group, 1 as have
     from users cross join groups;

Now clone the group requirements for each user:

   create view need as
    select distinct users.userid, groups.*
     from users cross join groups;

Finally, do the appropriate outer join on 4 keys (USERID, GROUP, PARTNER,
METHOD), but use the NATURAL keyword to cut down on the coding:

   create table eligible as
    select need.userid, need.group
     from have natural right join need
     group by need.userid, need.group
     having nmiss(have)=0;

It's a right join because credentials required but not held are pivotal, but
those held but not required are irrelevant. The HAVING condition sheds
USERID/GROUP pairs which are missing some credentials.

Result:

   userid  group

        1  one
        1  two

The code code be rolled up into one statement with inline views.