topic Re: re: Selecting Data in SAS Programming

re: Selecting Data

twildone — Wed, 16 Nov 2016 02:57:04 GMT

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

Re: re: Selecting Data

Reeza — Tue, 15 Nov 2016 23:11:26 GMT

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;

Re: re: Selecting Data

Astounding — Wed, 16 Nov 2016 02:31:31 GMT

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

Re: re: Selecting Data

twildone — Wed, 16 Nov 2016 03:00:42 GMT

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

Re: re: Selecting Data

mkeintz — Wed, 16 Nov 2016 08:30:27 GMT

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