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

Need SAS procedure!

 

I have a situation where I have duplicate records but there is one variable that is different and desire to keep that specific record. For instance:

 

1)  Customer Name    Customer ID  Address      Customer Type

       Joe Doe                  123              123 Way       Online            (retain)

       Joe Doe                  123              123 Way       In-Store         (delete)

       Ken Moore              456              456 Way       Online           (retain)

       Ken Moore              456              456 Way       In-Store         (delete)

       Lisa Mae                 789              789 Way       In-Store         (retain)

 

I want to keep the "Online" record (if duplicates) and delete the "In-Store" records. However, when there are no duplicates I retain "In-Store" records.

 

Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

proc sort data=have out=want;
  by customer_name customer_id address descending customer_type;
run;
proc sort data=want nodupkey;
by customer_name customer_id address;
run;

The first sort with descending customer_type is key, it will sort it so that online is always before in-store if present, the second sort with  nodupkey will just take the first observation which should be online if present, or in-store if not.

Note, not tested, post test data in the form of a datastep to get tested code.

 

View solution in original post

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

 

proc sort data=have out=want;
  by customer_name customer_id address descending customer_type;
run;
proc sort data=want nodupkey;
by customer_name customer_id address;
run;

The first sort with descending customer_type is key, it will sort it so that online is always before in-store if present, the second sort with  nodupkey will just take the first observation which should be online if present, or in-store if not.

Note, not tested, post test data in the form of a datastep to get tested code.

 

DrBigAl
Fluorite | Level 6

This worked! Thank you very much...

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 19256 views
  • 0 likes
  • 2 in conversation