DATA Step, Macro, Functions and more

re: Selecting Data

Accepted Solution Solved
Reply
Regular Contributor
Posts: 229
Accepted Solution

re: Selecting Data

[ Edited ]

Hi, I have a dataset that I want to select only those customer records (rows) from the dataset if there is at least once change in the product that the customer has purchased. The dataset has 20 variables (columns) the selection is based on the Product number for each Customer_ID. Any suggestions how to set this up. Thanks....

 

Have:

Customer_ID Product Date
2332 237 20160201
2332 237 20160310
2332 293 20160405
2332 293 20160523
2925 237 20160117
2925 237 20160224
2925 237 20160324
2925 237 201604412
2925 237 20160530
2925 237 20160627
3119 293 20160402
3119 293 20160515
3119 293 20160618
3254 293 20160214
3254 293 20160310
3254 237 20160415
3254 237 20160520
3254 293 20160629

 

 

Want:

Customer_ID Product Date
2332 237 20160201
2332 237 20160310
2332 293 20160405
2332 293 20160523
     
3254 293 20160214
3254 293 20160310
3254 237 20160415
3254 237 20160520
3254 293 20160629

Accepted Solutions
Solution
‎11-16-2016 08:07 PM
Trusted Advisor
Posts: 1,018

Re: re: Selecting Data

This is a common problem for the double DOW (typically two DO WHILE or DO UNTIL groups with embedded SET statements).  The first DO group counts product changes in an ID.  Note there could be only 2 products, but if they alternate there would be more than 1 change.  Since data are sorted by ID, but not product within ID the program has the NOTSORTED keyword in the first BY statement.

 

 

data want;
  do nchange=0 by 1 until (last.id);
    do until (last.product);
      set have;
      by id product notsorted;
    end;
  end;
  do until (last.id);
    set have;
    by id;
    if nchange>0 then output;
  end;
run;

 

Regards,

Mark

View solution in original post


All Replies
Super User
Posts: 19,770

Re: re: Selecting Data

So you want customers who have more than one product at any point in time.

I think the following will work if you have product as either character or numeric. 

 

 

proc sql;

create table want as

select *

from have

group by customer_id

having max(product) ne min(product);

quit;

Super User
Posts: 5,497

Re: re: Selecting Data

Why are you selecting 1 observation for customer 2925, but no observations for customer 3119?

Regular Contributor
Posts: 229

Re: re: Selecting Data

Posted in reply to Astounding

Hi Astounding.....Customers 2925 and 3119 should be excluded as they purchase the same product every time. It was my error...sorry about that.

Solution
‎11-16-2016 08:07 PM
Trusted Advisor
Posts: 1,018

Re: re: Selecting Data

This is a common problem for the double DOW (typically two DO WHILE or DO UNTIL groups with embedded SET statements).  The first DO group counts product changes in an ID.  Note there could be only 2 products, but if they alternate there would be more than 1 change.  Since data are sorted by ID, but not product within ID the program has the NOTSORTED keyword in the first BY statement.

 

 

data want;
  do nchange=0 by 1 until (last.id);
    do until (last.product);
      set have;
      by id product notsorted;
    end;
  end;
  do until (last.id);
    set have;
    by id;
    if nchange>0 then output;
  end;
run;

 

Regards,

Mark

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 250 views
  • 0 likes
  • 4 in conversation