BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kanyange
Fluorite | Level 6

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_IDTRAN_DTPROD_NM
1044267355701Oct2014Food to Go
1044267355701Oct2014Dairy
1044267355701Oct2014Impulse
1044267355701Oct2014Impulse
1044267355701Oct2014Impulse
1044267355802Oct2014Bakery
1044267355802Oct2014Bakery
1044267355802Oct2014Meat
1044267355802Oct2014Dairy
1044267355802Oct2014Dairy
1044267355802Oct2014Fruit

That's What I want

TRAN_IDTRAN_DTPROD_NM
1044267355701Oct2014Food to Go
1044267355701Oct2014Dairy
1044267355701Oct2014Impulse
1044267355802Oct2014Bakery
1044267355802Oct2014Meat
1044267355802Oct2014Dairy
1044267355802Oct2014Fruit
1 ACCEPTED SOLUTION

Accepted Solutions
MikeZdeb
Rhodochrosite | Level 12

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

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
MikeZdeb
Rhodochrosite | Level 12

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
Fluorite | Level 6

Many Thanks Mike...It works perfectly 🙂


Kanyange
Fluorite | Level 6

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
Fluorite | Level 6

Hi Mike,

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

Thanks again!

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 839 views
  • 0 likes
  • 3 in conversation