Delete rows on a condition but dont delete if there is only one record

[bharath86]

@Kurt_Bremser @hhinohar 

Hi I have a query please advise. 

Data I have:

ID	Tx	Flag
1	NA	Unknown
1	yes	Y
1	yes	Y
2	NA	Unknown
3	NA	Unknown
3	yes	Y
3	yes	Y

Data I Want:

ID	Tx	Flag
1	yes	Y
1	yes	Y
2	NA	Unknown
3	yes	Y
3	yes	Y

I want to delete those records which has Tx as 'NA' and Flag as 'Unknown', however if there is only one record for any ID, I dont want to delete that record even if the condition satisfies. 

Ex: For ID 2 there is only one record but it satisfies my condition to delete but I dont want to delete ID 2 as there is only one record. 

Please advise. 

Thank you

[PeterClemmensen · Accepted solution]

Try this

data have;
input ID Tx $ Flag $;
datalines;
1 NA  Unknown
1 yes Y      
1 yes Y      
2 NA  Unknown
3 NA  Unknown
3 yes Y      
3 yes Y      
;

data want;
   set have;
   by id;
   if Tx = 'NA' & Flag = 'Unknown' & sum(first.id, last.id) ne 2 then delete;
run;

Result:

ID Tx  Flag 
1  yes Y 
1  yes Y 
2  NA  Unknown 
3  yes Y 
3  yes Y 

[hhinohar]

Oh gee.I did a slight test of double if. I hope somebody corrects it.

data have;
infile datalines dlm="09"x;
input ID Tx $ Flag $;
datalines;
1	NA	Unknown
1	yes	Y
1	yes	Y
2	NA	Unknown
3	NA	Unknown
3	yes	Y
3	yes	Y
;
run;

data want;
	set have;
	by id;
	*Double if statement;
	if tx="NA" and upcase(FLAG)="UNKNOWN" then
		if not first.id or not last.id then delete;
run;

[bharath86]

Hey,

Thank you for the response. All three solutions are working actually. I just haveto accept the first one as he deserves it.

[Kurt_Bremser]

data want;
set have;
by id;
if (first.id and last.id /* only one obs */) or tx ne "NA" or flag ne "Unknown";
run;

If it is possible that an id has only multiple "NA"/"Unknown" observations, a double DO loop must be used to determine that beforehand.

[bharath86]

Hey,
Thank you for the response. You been amazingly helping me here.