topic Re: Logic to find who all are the customers who only uses gold card? in SAS Programming

Logic to find who all are the customers who only uses gold card?

ROHINISDAS — Fri, 25 Nov 2016 14:52:14 GMT

Hi ,

Below mentioned is the data from which i want to find the customer details who only uses gold card. I found the customers who uses whole three cards by using first. and last. concept. Now i am not able to put the logic how to find the

1)customers who only uses gold card.

2) customers who uses platinum but not gold

3)customers who uses titanium but not gold

Please help me out in solving the questions?

Re: Logic to find who all are the customers who only uses gold card?

Haikuo — Fri, 25 Nov 2016 15:02:46 GMT

If using a 2XDOW, you will be able to have it done in Data step, however, SQL is just much simpler:



/*1)customers who only uses gold card.
*/
proc sql;
select * from have
group by customer
having sum(x ne 'gold') =0;
quit;


/*2) customers who uses platinum but not gold 
*/

proc sql;
select * from have
group by customer
having sum(x eq 'Plat') >0 and sum(x eq 'gold') =0;
quit;

/*3)customers who uses titanium but not gold
*/
proc sql;
select * from have
group by customer
having sum(x eq 'tita') >0 and sum(x eq 'gold') =0;
quit;

Re: Logic to find who all are the customers who only uses gold card?

mkeintz — Fri, 25 Nov 2016 16:50:04 GMT

Simpler yes, but not by much.

The suggested SQL aproach requires 3 separate passes through the data, while the single DOW (don't need double DOW if the OP really meant it when he said he wanted "customer details" rather than customer transactions), requires only one pass, as per this program:

And, like virutally all cases where incoming records are already ordered by criteria pertinent to the problem, it will be faster than SQL - especially for large data sets.

data gold_only (keep=customer ntrans)
     plat_nogold (keep=customer ntrans ng np)
     tita_nogold (keep=customer ntrans ng nt);

  ng=0; np=0; nt=0;

  do ntrans=1 by 1 until (last.customer);
    set have;
    by customer;
    select (x);
      when ('gold') ng=ng+1;
      when ('plat') np=np+1;
      when ('tita') nt=nt+1;
    end;
  end;
  if ng=ntrans then output gold_only; else
  if ng=0 and np>0 then output plat_nogold; else
  if ng=0 and nt>0 then output tita_nogold;
run;

Now if the OP does want customer transactions based on the specified customer classification, then I would argue this double dow is not paticulary complicated, and teaches the user an important attribute about the likely advantages of sequential processing in sas.

data gold_only     plat_nogold     tita_nogold ;

  ng=0; np=0; nt=0;
  do ntrans=1 by 1 until (last.customer);
    set have;
    by customer;
    select (x);
      when ('gold') ng=ng+1;
      when ('plat') np=np+1;
      when ('tita') nt=nt+1;
    end;
  end;
  /* re-read the same group of records*/
  do until last.customer;
    set have;
    by customer;
    if ng=ntrans     then output gold_only;   else
    if ng=0 and np>0 then output plat_nogold; else
    if ng=0 and nt>0 then output tita_nogold;
  end;
run;

Re: Logic to find who all are the customers who only uses gold card?

ROHINISDAS — Sat, 26 Nov 2016 15:55:41 GMT

i didn't understand the logic. could u please explain. i am not the getting it.

Re: Logic to find who all are the customers who only uses gold card?

mkeintz — Sat, 26 Nov 2016 18:52:38 GMT

Whenever you see a structure like "do .... until (last.customer)" with a SET statement (or merge or update statement) inside the do group, it means you want to read all the records for a given customer.

In the do group in the first program and the first do group of the second program, the purpose is to count the number of times X='gold' (variable ng), x='Plat' (np) and x='tita'.   The SELECT group in my program is a substitue for
    if x='gold' then ng=ng+1; else
    if x='plat' then np=np+1; else

if x='tita' then nt=nt+1;

But the first do group also has DO ntrans=1 by 1 until (last.customer), which just means to initialize ntrans to a 1 and increment by 1 for every iteration of the loop. At the end of the loop NTRANS will be total number of all types of transaction. It can then be compared to NG to identify gold-only customers.

And the other IF ... then output ... should be self-evident.

The first program outputs 1 record per customer, i.e. you only wanted the id by customer type.

But the second program shows how to output all the transactions by customer type (not by transaction type). Because it requires output of transcations, it means that customer's transaction records have to be re-read, and explicitly output. Therefore there is a second "do until (last.customer)" group with an embedded SET statement, and also an embedded OUTPUT statement.

The take-home point here is that whenever you see two SET statements reading a given data set, they form two independent streams of data, neither SET picks up where the other left off.

regards

Mark