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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.