11-25-2016 09:52 AM
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?
11-25-2016 10:02 AM
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;
11-25-2016 11:50 AM
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;
11-26-2016 01:52 PM
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.