topic Re: How to delete row(s) for and ID if conditions are met? in SAS Programming

How to delete row(s) for and ID if conditions are met?

d0816 — Mon, 22 Apr 2019 14:36:19 GMT

Hi,

I have following sample dataset, where I am trying to look up all the rows for an ID and delete the row or rows if End Date and End Reason are missing for an ID but if all the other variables have the same data values considering all the rows for an ID.

For e.g. There are two rows for an ID 1, I would like to delete the second row.

There are three rows for ID 19, all three rows are a keep.

Would appreciate any suggestions to solve this using Proc SQL and/or data steps.

Thank you.

Start Month	Start Date	End Date	Class	End Reason	ID	College	Location	Location Number	Session	College Name
2015-03	2015-03-04	2015-05-04	A	SC	1	U	M	40	TM	U
2015-03	2015-03-04		A		1	U	M	40	TM	U
2016-07	2016-07-28	2016-09-21	A	SC	2	U	M	40	TM	U
2016-09	2016-09-22	2016-09-30	A	SC	2	U	M	40	TM	U
2017-06	2017-06-18	2017-07-31	A	SC	3	EM	M	40	TM	EM
2017-08	2017-08-14	2017-09-15	A	UNSC/INT	3	EM	M	40	TM	EM
2016-10	2016-10-06	2016-11-30	A	UNSC/INT	4	EM	M	40	TM	EM
2017-03	2017-03-27	2017-06-30	A	UNSC/INT	4	EM-MR	M	40	MR	EM-MR
2018-01	2018-01-29	2018-03-29	A	SC	5	U	M	40	TM	U
2018-03	2018-03-30	2018-04-10	A	SC	5	U	M	40	TM	U
2017-04	2017-04-06	2017-12-26	A	SC	6	NWC	ASH	02	T	NWC
2017-12	2017-12-28	2018-02-26	A	SC	6	NWC	ASH	02	T	NWC
2014-09	2014-09-21	2014-09-21	A	FTP-NGC	7	U	M	40	TM	U
2014-10	2014-10-24	2014-10-30	A	UNSC/INT	7	U	M	40	TM	U
2015-06	2015-06-15	2015-08-13	A	SC	8	U	M	40	TM	U
2015-08	2015-08-14	2015-08-30	A	SC	8	U	M	40	TM	U
2016-10	2016-10-31	2016-10-31	A	SC	9	U	M	40	TM	U
2016-11	2016-11-01	2016-12-31	A	SC	9	U	M	40	TM	U
2016-04	2016-04-11	2016-06-30	A	UNSC/INT	10	EM	M	40	TM	EM
2016-04	2016-04-11		A		10	EM	M	40	TM	EM
2014-08	2014-08-11	2014-09-30	A	UNSC/INT	11	U	M	40	TM	U
2014-11	2014-11-13	2015-07-07	A	SC	11	U	M	40	TM	U
2015-10	2015-10-19	2015-10-30	A	UNSC/INT	12	U	M	40	TM	U
2015-10	2015-10-19		A		12	U	M	40	TM	U
2015-06	2015-06-30	2015-08-28	A	SC	13	U	M	40	TM	U
2015-08	2015-08-29	2015-08-30	A	SC	13	U	M	40	TM	U
2018-03	2018-03-21	2019-02-27	A	RWC	14	NWC	TAY	60	T	NWC
2019-02	2019-02-28	2019-02-28	A	FTP-NGC	14	NWC	TAY	60	T	NWC
2016-05	2016-05-17	2016-05-30	A	UNSC/INT	15	U	M	40	TM	U
2016-05	2016-05-17		A		15	U	M	40	TM	U
2015-06	2015-06-19	2015-06-19	A	SC	16	U	M	40	TM	U
2015-06	2015-06-20	2015-08-30	A	SC	16	U	M	40	TM	U
2018-07	2018-07-30	2018-09-27	A	SC	17	U	M	40	TM	U
2018-09	2018-09-28	2018-10-30	A	SC	17	U	M	40	TM	U
2016-01	2016-01-12	2016-07-11	A	SC	18	U	M	40	TM	U
2016-01	2016-01-12		A		18	U	M	40	TM	U
2019-01	2019-01-08	2019-02-27	A	RWC	19	NWC	ASH	02	T	NWC
2019-02	2019-02-28		A		19	NWC	ASH	02	T	NWC
2019-02	2019-02-28		A		19	NWC	PRI	50	T	NWC

Re: How to delete row(s) for and ID if conditions are met?

Astounding — Mon, 22 Apr 2019 15:14:58 GMT

First sort your data if it is not already in sorted order:

proc sort data=have;
   by id start_month start_date class college location location_number session college_name end_date end_reason;
run;

Then remove the records you don't want:

data want;
   set have;
   by id start_month start_date class college location location_number session college_name end_date end_reason;
   if last.college_name=0 and (end_date=. or end_reason=' ') then delete;
run;

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Mon, 22 Apr 2019 16:03:08 GMT

HI, Thanks for your reply.

I tried the code you suggested but this is not working the way I want. This keeps one record per ID but I want to delete records only if value for two variables are missing AND all the other remaining variables have same data values for the same ID with multiple rows.

Re: How to delete row(s) for and ID if conditions are met?

novinosrin — Mon, 22 Apr 2019 16:24:21 GMT

Hi @d0816 Would it be possible to post your sample as datastep or replace the blanks with . , so that respondents can copy paste, test plz?

Re: How to delete row(s) for and ID if conditions are met?

Astounding — Mon, 22 Apr 2019 16:25:32 GMT

OK, let's start by changing OR to AND:

(end_date=. and end_reason=' ')

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Mon, 22 Apr 2019 19:08:29 GMT

@novinosrin

I replaced the blanks with a . , Please see below.

Start_Month	Start_Date	End_Date	Class	End_Reason	ID	College	Location	Location_Number	Session	College_Name
2015-03	3/4/2015	5/4/2015	A	SC	1	U	M	40	TM	U
2015-03	3/4/2015	.	A	.	1	U	M	40	TM	U
2016-07	7/28/2016	9/21/2016	A	SC	2	U	M	40	TM	U
2016-09	9/22/2016	9/30/2016	A	SC	2	U	M	40	TM	U
2017-06	6/18/2017	7/31/2017	A	SC	3	EM	M	40	TM	EM
2017-08	8/14/2017	9/15/2017	A	UNSC/INT	3	EM	M	40	TM	EM
2016-10	10/6/2016	11/30/2016	A	UNSC/INT	4	EM	M	40	TM	EM
2017-03	3/27/2017	6/30/2017	A	UNSC/INT	4	EM-MR	M	40	MR	EM-MR
2018-01	1/29/2018	3/29/2018	A	SC	5	U	M	40	TM	U
2018-03	3/30/2018	4/10/2018	A	SC	5	U	M	40	TM	U
2017-04	4/6/2017	12/26/2017	A	SC	6	NWC	ASH	2	T	NWC
2017-12	12/28/2017	2/26/2018	A	SC	6	NWC	ASH	2	T	NWC
2014-09	9/21/2014	9/21/2014	A	FTP-NGC	7	U	M	40	TM	U
2014-10	10/24/2014	10/30/2014	A	UNSC/INT	7	U	M	40	TM	U
2015-06	6/15/2015	8/13/2015	A	SC	8	U	M	40	TM	U
2015-08	8/14/2015	8/30/2015	A	SC	8	U	M	40	TM	U
2016-10	10/31/2016	10/31/2016	A	SC	9	U	M	40	TM	U
2016-11	11/1/2016	12/31/2016	A	SC	9	U	M	40	TM	U
2016-04	4/11/2016	6/30/2016	A	UNSC/INT	10	EM	M	40	TM	EM
2016-04	4/11/2016	.	A	.	10	EM	M	40	TM	EM
2014-08	8/11/2014	9/30/2014	A	UNSC/INT	11	U	M	40	TM	U
2014-11	11/13/2014	7/7/2015	A	SC	11	U	M	40	TM	U
2015-10	10/19/2015	10/30/2015	A	UNSC/INT	12	U	M	40	TM	U
2015-10	10/19/2015	.	A	.	12	U	M	40	TM	U
2015-06	6/30/2015	8/28/2015	A	SC	13	U	M	40	TM	U
2015-08	8/29/2015	8/30/2015	A	SC	13	U	M	40	TM	U
2018-03	3/21/2018	2/27/2019	A	RWC	14	NWC	TAY	60	T	NWC
2019-02	2/28/2019	2/28/2019	A	FTP-NGC	14	NWC	TAY	60	T	NWC
2016-05	5/17/2016	5/30/2016	A	UNSC/INT	15	U	M	40	TM	U
2016-05	5/17/2016	.	A	.	15	U	M	40	TM	U
2015-06	6/19/2015	6/19/2015	A	SC	16	U	M	40	TM	U
2015-06	6/20/2015	8/30/2015	A	SC	16	U	M	40	TM	U
2018-07	7/30/2018	9/27/2018	A	SC	17	U	M	40	TM	U
2018-09	9/28/2018	10/30/2018	A	SC	17	U	M	40	TM	U
2016-01	1/12/2016	7/11/2016	A	SC	18	U	M	40	TM	U
2016-01	1/12/2016	.	A	.	18	U	M	40	TM	U
2019-01	1/8/2019	2/27/2019	A	RWC	19	NWC	ASH	2	T	NWC
2019-02	2/28/2019	.	A	.	19	NWC	ASH	2	T	NWC
2019-02	2/28/2019	.	A	.	19	NWC	PRI	50	T	NWC

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Mon, 22 Apr 2019 19:11:10 GMT

I think this will still delete the rows I want to keep for an ID. For. eg. for ID 19, using this code will delete both 2nd and third row.

Re: How to delete row(s) for and ID if conditions are met?

Tom — Mon, 22 Apr 2019 19:25:46 GMT

How is what you want different than PROC SORT ?

proc sort data=have out=want nodupkey;
  by _all_;
run;

What about if you first used an UPDATE statement to carry forward values over missing END_DATE/END_REASON and then sorted to eliminate duplicate rows?

data fixed ;
  update have(keep=id end_date end_reason obs=0) have(keep=id end_date end_reason);
   by id;
   set have (drop=id end_date end_reason);
  output;
run;
proc sort data=fixed out=want nodupkey;
  by _all_;
run;

Re: How to delete row(s) for and ID if conditions are met?

Astounding — Mon, 22 Apr 2019 19:35:27 GMT

Good catch. Let's modify slightly:

proc sort data=have;
   by id start_month start_date class college location location_number session college_name end_date end_reason;
run;

data want;
   set have;
   by id start_month start_date class college location location_number session college_name end_date end_reason;
   if last.college_name and first.college_name then return;
   if last.college_name=0 and (end_date=. and end_reason=' ') then delete;
run;

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Mon, 22 Apr 2019 21:41:12 GMT

@Astounding This worked to delete the duplicate rows that meet the conditions.

Can you please tell me why these two line of codes (in green) were required and what they are actually doing?

if last.college_name and first.college_name then return;

if last.college_name=0 and (end_date=. and end_reason=' ') then delete;

Thanks so much for your help.

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Tue, 23 Apr 2019 13:45:56 GMT

@Tom I tried the code with UPDATE and then SORT. I got the error message that By variables are not properly sorted on the dataset.

Re: How to delete row(s) for and ID if conditions are met?

novinosrin — Tue, 23 Apr 2019 14:34:27 GMT




data have;
input (Start_Month Start_Date	End_Date	Class	End_Reason) ($)	ID	(College	Location	Location_Number	Session	College_Name) ($);
cards;
2015-03	3/4/2015	5/4/2015	A	SC	1	U	M	40	TM	U
2015-03	3/4/2015	.	A	.	1	U	M	40	TM	U
2016-07	7/28/2016	9/21/2016	A	SC	2	U	M	40	TM	U
2016-09	9/22/2016	9/30/2016	A	SC	2	U	M	40	TM	U
2017-06	6/18/2017	7/31/2017	A	SC	3	EM	M	40	TM	EM
2017-08	8/14/2017	9/15/2017	A	UNSC/INT	3	EM	M	40	TM	EM
2016-10	10/6/2016	11/30/2016	A	UNSC/INT	4	EM	M	40	TM	EM
2017-03	3/27/2017	6/30/2017	A	UNSC/INT	4	EM-MR	M	40	MR	EM-MR
2018-01	1/29/2018	3/29/2018	A	SC	5	U	M	40	TM	U
2018-03	3/30/2018	4/10/2018	A	SC	5	U	M	40	TM	U
2017-04	4/6/2017	12/26/2017	A	SC	6	NWC	ASH	2	T	NWC
2017-12	12/28/2017	2/26/2018	A	SC	6	NWC	ASH	2	T	NWC
2014-09	9/21/2014	9/21/2014	A	FTP-NGC	7	U	M	40	TM	U
2014-10	10/24/2014	10/30/2014	A	UNSC/INT	7	U	M	40	TM	U
2015-06	6/15/2015	8/13/2015	A	SC	8	U	M	40	TM	U
2015-08	8/14/2015	8/30/2015	A	SC	8	U	M	40	TM	U
2016-10	10/31/2016	10/31/2016	A	SC	9	U	M	40	TM	U
2016-11	11/1/2016	12/31/2016	A	SC	9	U	M	40	TM	U
2016-04	4/11/2016	6/30/2016	A	UNSC/INT	10	EM	M	40	TM	EM
2016-04	4/11/2016	.	A	.	10	EM	M	40	TM	EM
2014-08	8/11/2014	9/30/2014	A	UNSC/INT	11	U	M	40	TM	U
2014-11	11/13/2014	7/7/2015	A	SC	11	U	M	40	TM	U
2015-10	10/19/2015	10/30/2015	A	UNSC/INT	12	U	M	40	TM	U
2015-10	10/19/2015	.	A	.	12	U	M	40	TM	U
2015-06	6/30/2015	8/28/2015	A	SC	13	U	M	40	TM	U
2015-08	8/29/2015	8/30/2015	A	SC	13	U	M	40	TM	U
2018-03	3/21/2018	2/27/2019	A	RWC	14	NWC	TAY	60	T	NWC
2019-02	2/28/2019	2/28/2019	A	FTP-NGC	14	NWC	TAY	60	T	NWC
2016-05	5/17/2016	5/30/2016	A	UNSC/INT	15	U	M	40	TM	U
2016-05	5/17/2016	.	A	.	15	U	M	40	TM	U
2015-06	6/19/2015	6/19/2015	A	SC	16	U	M	40	TM	U
2015-06	6/20/2015	8/30/2015	A	SC	16	U	M	40	TM	U
2018-07	7/30/2018	9/27/2018	A	SC	17	U	M	40	TM	U
2018-09	9/28/2018	10/30/2018	A	SC	17	U	M	40	TM	U
2016-01	1/12/2016	7/11/2016	A	SC	18	U	M	40	TM	U
2016-01	1/12/2016	.	A	.	18	U	M	40	TM	U
2019-01	1/8/2019	2/27/2019	A	RWC	19	NWC	ASH	2	T	NWC
2019-02	2/28/2019	.	A	.	19	NWC	ASH	2	T	NWC
2019-02	2/28/2019	.	A	.	19	NWC	PRI	50	T	NWC
;


proc sql;
create table temp as
select *,count(*) as c1 
from
(select *,n(id) as c from have group by id)
group by Start_Month ,Start_Date,id,	Class,College,	Location,	Location_Number,	Session,	College_Name,c
order by id;
quit;


data want;
set temp;
by id;
if c=c1 and cmiss(end_date,end_reason)=2 then delete;
drop c c1;
run;

EDITED to correct cmiss(end_date,end_reason)=2

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Tue, 23 Apr 2019 18:50:44 GMT

@novinosrin Thank you this solution using Proc SQL. The code is working for the sample dataset I provided.

However, If there were only one row for an ID with missing End_date and End_Reason (meaning that the "Class" is ongoing), those got deleted too. I did not have a case like this in the sample dataset and I was not clear on this. But I do not want to delete IDs with only one row.For eg.

Start_Month	Start_Date	End_Date	Class	End_Reason	ID	College	Location	Location_Number	Session	College_Name
2018-11	2018-11-05	.	A	SC	20	U	M	40	TM	U
2019-02	2019-02-07	.	A	.	21	U	M	40	TM	U

For IDs with multiple rows, looking at all the rows for the ID, I want to delete those rows with missing End_date and End_Reason but all other data values are the same with one of the rows. If its only one row for an ID whether End_date and End_Reason are missing or not missing, I want to keep it.

Sorry, I was not clear on this early on.

Re: How to delete row(s) for and ID if conditions are met?

novinosrin — Tue, 23 Apr 2019 18:49:03 GMT

HI @d0816

A simple tweak should do I think.

Try:

Notice is the c>1

if c=c1 and cmiss(end_date,end_reason)=2 and c>1 then delete;

Re: How to delete row(s) for and ID if conditions are met?

Astounding — Wed, 24 Apr 2019 02:51:42 GMT

The first line in green applies when there is only one observation for the group of variables ending with COLLEGE_NAME. In that case, nothing should be deleted, so RETURN avoids the statement that follows.

The second line in green deletes observations that contain a missing END_DATE and END_REASON, but avoids deleting if it is the last observation for the group of variables. Because of the sorted order, if the last observation has a missing value for END_DATE and END_REASON, all the observations must have a missing value for END_DATE and END_REASON. So that line keeps one observation from the group when END_DATE and END_REASON are always missing.

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Fri, 26 Apr 2019 16:22:02 GMT

@novinosrin

I think that simple tweak worked for the said issue but again, I also have cases like below, where 2nd row for ID 50 and 1st row for ID 51 should be deleted but is not being deleted with the new tweaked code.

Thanks so much.

Start_Month	Start_Date	End_Date	Class	End_Reason	ID	College	Location	Location_Number	Session	College_Name
2014-10	2014-10-28	2014-10-31	A	UNSC/INT	50	U	M	40	TM	U
2014-11	2014-11-01		A	.	50	U	M	40	TM	U
2014-11	2014-11-01	2014-11-30	A	UNSC/INT	50	U	M	40	TM	U
2016-04	2016-04-29		A	.	51	U	M	40	TM	U
2016-04	2016-04-29	2016-04-29	A	SC	51	U	M	40	TM	U
2018-06	2018-06-11	2018-08-30	A	SC	51	U	M	40	TM	U

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Fri, 26 Apr 2019 16:24:09 GMT

@Astounding

Thank you so much for the explanation. It really helped understand.

Re: How to delete row(s) for and ID if conditions are met?

novinosrin — Fri, 26 Apr 2019 16:38:07 GMT

Hi @d0816 I think the "group by combination" vs "group by id" is something that needs a fix. But since that you have a working solution , we could care less . If it comes to another thread where this approach would really be needed, we shall delve into it.

Re: How to delete row(s) for and ID if conditions are met?

d0816 — Mon, 29 Apr 2019 15:10:35 GMT

@novinosrin Sounds good. Thank you.