Hi,
I have some transaction data and I would like to retain the order of the products the customer have bought but I want to delete the duplicates...for example, if in one tranaction they buy first Milk, Egg, Egg , Fruit. I just want to keep Milk, Egg and Fruit. Basically I don't want to use select distinct as I will loose the order of buying.Please find below the Input and the output I would like to have. Thank you
That's the table I have
TRAN_ID | TRAN_DT | PROD_NM |
10442673557 | 01Oct2014 | Food to Go |
10442673557 | 01Oct2014 | Dairy |
10442673557 | 01Oct2014 | Impulse |
10442673557 | 01Oct2014 | Impulse |
10442673557 | 01Oct2014 | Impulse |
10442673558 | 02Oct2014 | Bakery |
10442673558 | 02Oct2014 | Bakery |
10442673558 | 02Oct2014 | Meat |
10442673558 | 02Oct2014 | Dairy |
10442673558 | 02Oct2014 | Dairy |
10442673558 | 02Oct2014 | Fruit |
That's What I want
TRAN_ID | TRAN_DT | PROD_NM |
10442673557 | 01Oct2014 | Food to Go |
10442673557 | 01Oct2014 | Dairy |
10442673557 | 01Oct2014 | Impulse |
10442673558 | 02Oct2014 | Bakery |
10442673558 | 02Oct2014 | Meat |
10442673558 | 02Oct2014 | Dairy |
10442673558 | 02Oct2014 | Fruit |
Hi. Try this ...
data y;
set x;
by tran_id tran_dt prod_nm notsorted;
if first.prod_nm;
run;
proc print data=y;
run;
Obs TRAN_ID TRAN_DT PROD_NM
1 10442673557 01OCT2014 Food to Go
2 10442673557 01OCT2014 Dairy
3 10442673557 01OCT2014 Impulse
4 10442673558 02OCT2014 Bakery
5 10442673558 02OCT2014 Meat
6 10442673558 02OCT2014 Dairy
7 10442673558 02OCT2014 Fruit
Create a sequence variable to retain the order, then delete the second and third (and additional...) occurrences, then sort by the order variable.
Hi. Try this ...
data y;
set x;
by tran_id tran_dt prod_nm notsorted;
if first.prod_nm;
run;
proc print data=y;
run;
Obs TRAN_ID TRAN_DT PROD_NM
1 10442673557 01OCT2014 Food to Go
2 10442673557 01OCT2014 Dairy
3 10442673557 01OCT2014 Impulse
4 10442673558 02OCT2014 Bakery
5 10442673558 02OCT2014 Meat
6 10442673558 02OCT2014 Dairy
7 10442673558 02OCT2014 Fruit
Many Thanks Mike...It works perfectly 🙂
Hi Mike,
Sorry I have just realized that the code you sent over will sort the prod_nm by alphabetic order....!!
I thought it says 'notsorted'?
Thank You
Hi Mike,
Sorry apologies, it works good 😞 I think it's time for me to go home...
Thanks again!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.