topic Data Manipulation, please help..Thank You in SAS Procedures

Data Manipulation, please help..Thank You

Kanyange — Thu, 11 Dec 2014 13:02:48 GMT

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

Re: Data Manipulation, please help..Thank You

PaigeMiller — Thu, 11 Dec 2014 13:13:59 GMT

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

Re: Data Manipulation, please help..Thank You

MikeZdeb — Thu, 11 Dec 2014 14:37:31 GMT

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

Re: Data Manipulation, please help..Thank You

Kanyange — Thu, 11 Dec 2014 16:19:22 GMT

Many Thanks Mike...It works perfectly 🙂

Re: Data Manipulation, please help..Thank You

Kanyange — Thu, 11 Dec 2014 16:44:11 GMT

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

Re: Data Manipulation, please help..Thank You

Kanyange — Thu, 11 Dec 2014 17:15:13 GMT

Hi Mike,

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

Thanks again!