I am trying to get rid of duplicates in my data. I have decided a duplicate occurs if every column is the same except for code. I want to write a function to get rid of duplicates based on how I have defined them.
Below is an example of my current data set, and what I wish my desired data set to look like.
current data
ID | Code | Lodge date | app date | Choice | Category | Rule | Dollars |
1280 | 1 | 1/04/2019 8:25 | 1/04/2019 | D | 1 | D005R015 | -26804 |
2125 | 2 | 1/04/2019 10:16 | 8/07/2019 | A | 1 | D005R015 | -5478 |
2125 | 3 | 1/04/2019 10:16 | 8/07/2019 | A | 1 | D005R015 | -5478 |
2413 | 4 | 1/04/2019 10:17 | 10/06/2019 | A | 2 | R015 | 253 |
2413 | 4 | 1/04/2019 10:17 | 10/06/2019 | A | 2 | R015 | 241 |
desired data
ID | Code | Lodge date | app date | Choice | Category | Rule | Dollars |
1280 | 1 | 1/04/2019 8:25 | 1/04/2019 | D | 1 | D005R015 | -26804 |
2125 | 2 | 1/04/2019 10:16 | 8/07/2019 | A | 1 | D005R015 | -5478 |
2413 | 4 | 1/04/2019 10:17 | 10/06/2019 | A | 2 | R015 | 253 |
2413 | 4 | 1/04/2019 10:17 | 10/06/2019 | A | 2 | R015 | 241 |
Thanks.
try this code.
proc sort data=have out=want nodupkey;
by ID Lodge_data--Dollars;
run;
Use Proc Sort and sort by all variables that you want to look at (everything except code) and use keyword NODUPKEY.
try this code.
proc sort data=have out=want nodupkey;
by ID Lodge_data--Dollars;
run;
@japelin Please note that what you propose will only work if the variables in the actual table are really in the order as shown in the reports.
@Patrick Thank you very much.
I wrote it in a simplified way because wanted to remove duplicates in variables other than Code, but the point is important.
@sasprogramming Please make sure that the order of the variables in the dataset matches the order of the variables you have presented!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.