BookmarkSubscribeRSS Feed
ROHINISDAS
Obsidian | Level 7

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?

 

secondquestion.JPG

4 REPLIES 4
Haikuo
Onyx | Level 15

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;


mkeintz
PROC Star

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ROHINISDAS
Obsidian | Level 7

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

mkeintz
PROC Star

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

 

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 907 views
  • 0 likes
  • 3 in conversation