## re: Selecting Data

Solved
Regular Contributor
Posts: 247

# 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
Posts: 1,346

## 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

All Replies
Super User
Posts: 23,791

## 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: 6,788

## Re: re: Selecting Data

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

Regular Contributor
Posts: 247

## Re: re: Selecting Data

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
Posts: 1,346

## 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.