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 |
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
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;
Why are you selecting 1 observation for customer 2925, but no observations for customer 3119?
Hi Astounding.....Customers 2925 and 3119 should be excluded as they purchase the same product every time. It was my error...sorry about that.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.