topic Re: Delete rows on a condition but dont delete if there is only one record in SAS Programming

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

bharath86 — Mon, 30 Nov 2020 12:09:59 GMT

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

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

PeterClemmensen — Mon, 30 Nov 2020 12:22:04 GMT

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

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

hhinohar — Mon, 30 Nov 2020 12:23:05 GMT

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;

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

Kurt_Bremser — Mon, 30 Nov 2020 14:09:55 GMT

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.

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

bharath86 — Mon, 30 Nov 2020 13:15:01 GMT

Hey,

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

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

bharath86 — Mon, 30 Nov 2020 13:15:41 GMT

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