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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.