topic Re: remove row based on data in columns in SAS Programming

remove row based on data in columns

sasprogramming — Mon, 10 May 2021 07:19:52 GMT

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.

Re: remove row based on data in columns

Patrick — Mon, 10 May 2021 07:30:28 GMT

Use Proc Sort and sort by all variables that you want to look at (everything except code) and use keyword NODUPKEY.

Re: remove row based on data in columns

japelin — Mon, 10 May 2021 07:34:07 GMT

try this code.

proc sort data=have out=want nodupkey;
  by ID Lodge_data--Dollars;
run;

Re: remove row based on data in columns

Patrick — Mon, 10 May 2021 07:38:03 GMT

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

Re: remove row based on data in columns

japelin — Mon, 10 May 2021 07:53:33 GMT

@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!