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...

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
  • 2 replies
  • 17042 views
  • 0 likes
  • 2 in conversation