Delete rows based on condition

[erfree18]

Hi,

I've merged two datasets by ID and TYPE. One dataset will be updated weekly, populating the CONTACT column. In this example, I'd like to delete rows 3 and 4, if TYPE = 8 and CONTACT = "Yes."

Would appreciate any suggestions.

Thank you.

OBS	ID	DATE	TYPE	CONTACT
1	2001	08/14/2023	7	No
2	2001	08/21/2023	8	Yes
3	2001	08/28/2023	9
4	2001	09/04/2023	10

[PaigeMiller]

---

@erfree18 wrote:

Hi,

I've merged two datasets by ID and TYPE. One dataset will be updated weekly, populating the CONTACT column. In this example, I'd like to delete rows 3 and 4, if TYPE = 8 and CONTACT = "Yes."

Would appreciate any suggestions.

Thank you.

OBS	ID	DATE	TYPE	CONTACT
1	2001	08/14/2023	7	No
2	2001	08/21/2023	8	Yes
3	2001	08/28/2023	9
4	2001	09/04/2023	10

---

Rows 3 and 4 should be deleted? Why? It doesn't seem to meet the condition you stated.

But anyway, to delete rows, you can use something like this

if <some condition is true> then delete;

[ballardw]

@PaigeMiller 

Care to make any wager that this is another case of "delete looking forward" for values of the ID variable.

And actually very likely can be fixed in the "I've merged two datasets by ID and TYPE. " step with appropriate use of In= variables.

[PaigeMiller]

The last time I made a wager was for the 1969 World Series, which I lost. I suspect you are right, but no wager offered.

[erfree18]

I didn't explain it well in the original post but I want to delete subsequent rows if the previous row meets the condition. I've done further exploring and it seems the code below works.

data want;
	set have;
	retain flag;
	if type = 8 then do;
		if contact = 'Yes' then flag = 1;
		else flag = 0;
	end;
	else if type in (9, 10) and flag = 1 then delete;
run;

[ballardw]

---

@erfree18 wrote:

Hi,

I've merged two datasets by ID and TYPE. One dataset will be updated weekly, populating the CONTACT column. In this example, I'd like to delete rows 3 and 4, if TYPE = 8 and CONTACT = "Yes."

Would appreciate any suggestions.

Thank you.

OBS	ID	DATE	TYPE	CONTACT
1	2001	08/14/2023	7	No
2	2001	08/21/2023	8	Yes
3	2001	08/28/2023	9
4	2001	09/04/2023	10

---

How about providing examples of the two sets and the code used to merge them that results in the above.

It may be easier to prevent such at the Merge step.

A dummy example of what I think you may want:

data want;  
   merge dataone (in=inone)
         datatwo (in=intwo)
   ;
   by id type;
   if inone and intwo;
run;

The data set option in= creates temporary variables that indicate that a data set contributes an observation to the current one. The variables are valued 1 for contributes, 0 for does not contribute.

So if you want only have records in the output set where there was a value from BOTH sets the above in the IF statement checks to see if both contribute.

OR if you need all of the records from one set (likely the one you wanted to add something from the other) then use IF with the variable created for that data set. Then observations from  the other set that did not exactly match will not be written to the output data set.