How to keep rows conditionally for an ID

[d0816]

I have following sample dataset that is sorted by ID and Class Start Date. For an "ID", I would like to keep those rows whenever ID has a NEW "Class Start Date" for the same or different "Class". When doing this, I want to keep the row with the earliest "Date".I have added a column to show which rows I need to keep for an ID. Any help is much appreciated. Thank You.

Date	ID	Class	Class Start Date	Class End Date	Days in Class	Months in College	Month Category	Keep or Delete
2018-08	1	AAA	2018-08-13	9999-12-31	63	20	13-24 Months	Keep
2018-09	1	AAA	2018-08-13	9999-12-31	63	21	13-24 Months
2018-10	1	AAA	2018-08-13	9999-12-31	63	21	13-24 Months
2018-08	2	BBB	2018-08-22	2018-09-30	40	1	0-12 Months	Keep
2018-09	2	BBB	2018-08-22	2018-09-30	40	2	0-12 Months
2017-10	3	CCC	2017-10-02	2017-10-22	21	0	0-12 Months	Keep
2017-10	3	BBB	2017-10-23	2017-11-28	37	0	0-12 Months	Keep
2017-11	3	BBB	2017-10-23	2017-11-28	37	1	0-12 Months
2017-11	3	BBB	2017-11-30	2018-02-28	91	1	0-12 Months	Keep
2017-12	3	BBB	2017-11-30	2018-02-28	91	2	0-12 Months
2018-01	3	BBB	2017-11-30	2018-02-28	91	3	0-12 Months
2018-02	3	BBB	2017-11-30	2018-02-28	91	4	0-12 Months
2018-03	3	BBB	2018-03-02	2018-05-11	71	4	0-12 Months	Keep
2018-04	3	BBB	2018-03-02	2018-05-11	71	6	0-12 Months
2018-05	3	BBB	2018-03-02	2018-05-11	71	7	0-12 Months
2018-08	4	AAA	2018-08-07	2018-08-31	25	8	0-12 Months	Keep
2018-09	4	AAA	2018-09-08	2018-09-30	23	9	0-12 Months	Keep
2017-03	5	BBB	2017-03-08	2017-03-12	5	1	0-12 Months	Keep
2017-03	5	CCC	2017-03-13	2017-05-31	80	1	0-12 Months	Keep
2017-04	5	CCC	2017-03-13	2017-05-31	80	1	0-12 Months
2017-05	5	CCC	2017-03-13	2017-05-31	80	1	0-12 Months
2018-07	5	AAA	2018-07-02	2018-08-31	61	8	0-12 Months	Keep
2018-08	5	AAA	2018-07-02	2018-08-31	61	9	0-12 Months
2017-10	6	BBB	2017-10-16	2017-11-30	46	1	0-12 Months	Keep
2017-11	6	BBB	2017-10-16	2017-11-30	46	2	0-12 Months
2017-12	6	BBB	2017-12-04	2018-04-30	148	3	0-12 Months	Keep
2018-01	6	BBB	2017-12-04	2018-04-30	148	4	0-12 Months
2018-02	6	BBB	2017-12-04	2018-04-30	148	5	0-12 Months
2018-03	6	BBB	2017-12-04	2018-04-30	148	6	0-12 Months
2018-04	6	BBB	2017-12-04	2018-04-30	148	7	0-12 Months
2018-01	7	CCC	2018-01-12	2018-01-31	20	0	0-12 Months	Keep
2017-03	8	CCC	2017-03-15	2017-05-11	58	0	0-12 Months	Keep
2017-04	8	CCC	2017-03-15	2017-05-11	58	0	0-12 Months
2017-05	8	CCC	2017-03-15	2017-05-11	58	1	0-12 Months
2017-05	8	BBB	2017-05-12	2017-08-31	112	1	0-12 Months	Keep
2017-06	8	BBB	2017-05-12	2017-08-31	112	2	0-12 Months
2017-07	8	BBB	2017-05-12	2017-08-31	112	3	0-12 Months
2017-08	8	BBB	2017-05-12	2017-08-31	112	4	0-12 Months
2017-11	8	BBB	2017-11-20	2018-03-31	132	5	0-12 Months	Keep
2017-12	8	BBB	2017-11-20	2018-03-31	132	6	0-12 Months
2018-01	8	BBB	2017-11-20	2018-03-31	132	7	0-12 Months
2018-02	8	BBB	2017-11-20	2018-03-31	132	8	0-12 Months
2018-03	8	BBB	2017-11-20	2018-03-31	132	8	0-12 Months
2017-11	9	AAA	2017-11-10	2017-12-31	52	12	0-12 Months	Keep
2017-12	9	AAA	2017-11-10	2017-12-31	52	13	13-24 Months
2018-04	9	AAA	2018-04-30	2018-07-05	67	16	13-24 Months	Keep
2018-05	9	AAA	2018-04-30	2018-07-05	67	17	13-24 Months
2018-06	9	AAA	2018-04-30	2018-07-05	67	18	13-24 Months
2018-07	9	AAA	2018-04-30	2018-07-05	67	19	13-24 Months
2018-03	10	AAA	2018-03-26	2018-08-31	159	72	61+ Months	Keep
2018-04	10	AAA	2018-03-26	2018-08-31	159	73	61+ Months
2018-05	10	AAA	2018-03-26	2018-08-31	159	74	61+ Months
2018-06	10	AAA	2018-03-26	2018-08-31	159	75	61+ Months
2018-07	10	AAA	2018-03-26	2018-08-31	159	76	61+ Months
2018-08	10	AAA	2018-03-26	2018-08-31	159	77	61+ Months
2018-09	10	AAA	2018-09-03	2018-09-05	3	78	61+ Months	Keep
2017-06	11	BBB	2017-06-05	2017-06-30	26	1	0-12 Months	Keep
2017-10	11	CCC	2017-10-23	2018-01-31	101	1	0-12 Months	Keep
2017-11	11	CCC	2017-10-23	2018-01-31	101	1	0-12 Months
2017-12	11	CCC	2017-10-23	2018-01-31	101	1	0-12 Months
2018-01	11	CCC	2017-10-23	2018-01-31	101	1	0-12 Months
2017-05	12	BBB	2017-05-30	2017-06-04	6	1	0-12 Months	Keep
2017-06	12	BBB	2017-05-30	2017-06-04	6	2	0-12 Months
2017-06	12	CCC	2017-06-05	2017-07-28	54	2	0-12 Months	Keep
2017-07	12	CCC	2017-06-05	2017-07-28	54	2	0-12 Months

[Kurt_Bremser · Accepted solution]

Sort by id, <class start date> and date. Then do a data step with by id and <class start date>, and use a subsetting if first.<class start date>

Insert the real names ("class start date" is not a valid SAS name).

[d0816]

Thank you so much. It worked.

[d0816]

HI,

I have similar problem but here I am trying to keep all rows with different session for an ID . IF an ID have multiple rows with same session name then, keep the row with the earliest session begin date for an ID.

For e.g for ID #3,5,7 having multiple rows with same session name, keep the row with the earliest begin date. For ID #6, with different session name, keep both the rows. Any suggestion?

Date	ID	Session	SessionBeginDate	TotalDaysinSession	Keep or Delete
2018-08	1	MMM	2018-08-13	63	Keep
2018-08	2	OOO	2018-08-22	40	Keep
2017-11	3	OOO	2017-11-30	199	Keep
2018-03	3	OOO	2018-03-02	199
2017-10	3	OOO	2017-10-23	199
2018-03	4	MMM	2018-03-07	41	Keep
2018-08	5	MMM	2018-08-07	48	Keep
2018-09	5	MMM	2018-09-08	48
2018-07	6	MMM	2018-07-02	61	Keep
2017-03	6	OOO	2017-03-08	5	Keep
2017-10	7	OOO	2017-10-16	194	Keep
2017-12	7	OOO	2017-12-04	194
2017-11	8	OOO	2017-11-20	244	Keep
2017-05	8	OOO	2017-05-12	244

[Kurt_Bremser]

---

@d0816 wrote:

HI,

I have similar problem but here I am trying to keep all rows with different session for an ID . IF an ID have multiple rows with same session name then, keep the row with the earliest session begin date for an ID.

For e.g for ID #3,5,7 having multiple rows with same session name, keep the row with the earliest begin date. For ID #6, with different session name, keep both the rows. Any suggestion?

Date	ID	Session	SessionBeginDate	TotalDaysinSession	Keep or Delete
2018-08	1	MMM	2018-08-13	63	Keep
2018-08	2	OOO	2018-08-22	40	Keep
2017-11	3	OOO	2017-11-30	199	Keep
2018-03	3	OOO	2018-03-02	199
2017-10	3	OOO	2017-10-23	199
2018-03	4	MMM	2018-03-07	41	Keep
2018-08	5	MMM	2018-08-07	48	Keep
2018-09	5	MMM	2018-09-08	48
2018-07	6	MMM	2018-07-02	61	Keep
2017-03	6	OOO	2017-03-08	5	Keep
2017-10	7	OOO	2017-10-16	194	Keep
2017-12	7	OOO	2017-12-04	194
2017-11	8	OOO	2017-11-20	244	Keep
2017-05	8	OOO	2017-05-12	244

---

Use the same method as previous, just with different variables (session!) in the by statements and the first. condition.