Grouping

[sasasauraus]

Hello SAS Users:

I have been tasked with turning the above matrix into a SAS program but having major difficulties covering all scenarios.

HAVE:
In the four right hand columns are scenarios where customers (A,B,C) open joint bank accounts. The Credit Card columns illustrate which customers have a credit card, the email columns show which customers have an email address on file. The reward columns show who receives the cash payout for customers who opened an account and possess a new credit card and have an email. Hence; for the first row customer A opens an account with customer B and B opens an account with C. Only A has a credit card and email; therefore only A receives the cash reward (this person holds an account and credit card and email address).

WANT:
The final four columns (REWARD CARD) is the end result. Below is dummy code I have played with - resulting in limited success. Anyone have a better solution to cover all scenarios off?

Many thanks everyone.

data temp1;
infile datalines truncover;
input customer $ acct $ email $ creditcrd $;

/* row 1 scenario */

datalines;
a 123 yy1 y
b 123
b 782
c 782

;

/* row 7 scenario

datalines;
a 123 yy1 y
b 123 ee2 y
b 782 ee2 y
c 782 ff3   y
a 999 yy1 y
c 999 ff3 y
;

*/

proc sort data=temp1     out=temp2      nodupkey;
       by customer;
            where email ne '' and creditcrd = 'y';
run;
/*

proc sort data=temp2    out=temp3    nodupkey;
       by acct;
run;

*/

proc sql;
create table want    as
   select * from
       (select * from
            (select *, count(*) as ct
                  from temp2
                     where email ne '' and creditcrd = 'y'
                          group by customer,acct)

         group by customer
              having ct=max(ct))

   group by customer
        having acct=max(acct)
;
quit;

[sasasauraus]

"In the four right hand columns are scenarios where customers (A,B,C) open joint bank accounts."

Sorry...it should read as, "In the four LEFT hand columns are scenarios where customers (A,B,C) open joint bank accounts."

[ballardw]

Can't code against pictures.

You data has so little variability it is hard to follow which A in the first column is used for what purpose in other columns.

At least spend the time to make "reasonable" dummy Email ID values so it does not appear that your entire project only has 3 emails. Or explain the significance of A, B, C in each block of values.

I do not find the logic obvious for what you actually have or how what you want is derived.

[sasasauraus]

Adding further clarification...

HAVE:

In the four left hand columns(blue shade) are scenarios where customers (A,B,C) open joint/or single bank accounts.  The Credit Card columns (green) illustrate which customers (A,B,C) have a credit card, the email columns(tan) show which customers have an email address on file.  The reward columns (beige) show who receives the cash payout for those customers who:

            (i) opened an account .....AND

            (ii) possess a credit card..... AND

            (iii) have an email address on file 

Therefore; if a customer(s) open a bank account AND have a credit card AND have an email on file they will receive a reward card payout as per the matrix above (REWARD CARD Columns).

For example - (row #1) customer A opens an account with customer B and B opens an account with C.  Only A has a credit card and email; therefore only A receives the cash reward (ie; only this person holds an account and credit card and email address = reward).

Row #2 example - customer A opens an account with customer B and B opens an account with C.  Both customers A and B have a credit card and email address.  Both customer A & B receive a reward card.

WANT:

The final four columns (REWARD CARD) dictates the resulting payouts which is the goal of this exercise.  Test Code I constructed is already appended to this thread.  Since this is dummy data built to test each row in the matrix there is no need to add more than 3 rows of email data (I am not testing emails).  I am looking to produce the customer A,B, C payout results dictated under the columns REWARD CARD based on their holdings of accounts + credit card + email address = reward card.

Would anyone have a better coding suggestion to cover ALL scenarios off in the matrix provided? 

Many thanks everyone!