Retain group variable is different then keep both records , else if group='Other' then keep only tis

[Stalk]

I have a specific requirement. Keep all the duplicate records if the group is different except in one instance If the record is unique with Group='Other' then keep this otherwise delete all duplicate records where group='Other'. Always take the most recent subDate

data to_clean;
infile cards dlm='|' truncover ;
input subDate :mmddyy10. unitName :$100. ADDR1 :$100. group $4.;
format subDate yymmdd10.;
cards;
11/21/2020|BAIRD HUDSON ENTERPRISES|106 E MAIN|Other
10/30/2020|BAIRD HUDSON ENTERPRISES|106 E MAIN STREET|TNF
10/30/2020|BIG HORN ENTERPRISE|146 S. BENT STREET|SNF
10/30/2020|BIG HORN ENTERPRISE|641 WARREN STREET|SNF
11/5/2020|BROOKDALE |tt|ALF
10/29/2020|BROOKDALE|2401 COUGAR AVENUE|ALF
10/30/2020|ELMCROFT|1551 SUGARLAND DRIVE|ALF
11/2/2020|ELMCROFT|1551 SUGARLAND DRIVE DRIVE|SNF
11/21/2020|GREEN HOUSE LIVING|2311 SHIRLEY|SNF
10/29/2020|GREEN HOUSE LIVING|2311 SHIRLEY COVE|ALF
11/21/2020|MISSION AT THE VILLA|1445 UINTA|ALF
11/2/2020|MISSION AT THE VILLA|1445 UINTA DRIVE|Other

[andreas_lds · Accepted solution]

Maybe this is a late-monday-effect (had a day off yesterday): i don't see a clear definition of "duplicate" in your description.

[Stalk]

Duplicates are across the multiple fields unitName, ADDR1,group;
If a record is unique and GROUP='Other' then keep
If a record is a duplicate and GROUP='Other', then delete that one.

[andreas_lds]

---

@Stalk wrote:
Duplicates are across the multiple fields unitName, ADDR1,group;
If a record is unique and GROUP='Other' then keep
If a record is a duplicate and GROUP='Other', then delete that one.

---

And what should happen to those observations with Group NE 'Other'?