Help using Base SAS procedures

Data Manipulation, please help..Thank You

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 95
Accepted Solution

Data Manipulation, please help..Thank You

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

Accepted Solutions
Solution
‎12-11-2014 09:37 AM
Valued Guide
Posts: 765

Re: Data Manipulation, please help..Thank You

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


All Replies
Trusted Advisor
Posts: 1,931

Re: Data Manipulation, please help..Thank You

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

Solution
‎12-11-2014 09:37 AM
Valued Guide
Posts: 765

Re: Data Manipulation, please help..Thank You

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

Frequent Contributor
Posts: 95

Re: Data Manipulation, please help..Thank You

Many Thanks Mike...It works perfectly :-)


Frequent Contributor
Posts: 95

Re: Data Manipulation, please help..Thank You

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


Frequent Contributor
Posts: 95

Re: Data Manipulation, please help..Thank You

Hi Mike,

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

Thanks again!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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