Data Manipulation, please help..Thank You

[Kanyange]

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

[PaigeMiller]

Create a sequence variable to retain the order, then delete the second and third (and additional...) occurrences, then sort by the order variable.

[MikeZdeb · Accepted solution]

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

[Kanyange]

Many Thanks Mike...It works perfectly 🙂

[Kanyange]

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

[Kanyange]

Hi Mike,

Sorry apologies, it works good 😞 I think it's time for me to go home...

Thanks again!