topic Re: Flag Duplicate Based on Multiple Criteria in SAS Programming

Flag Duplicate Based on Multiple Criteria

BlayLay — Thu, 12 Nov 2020 15:29:54 GMT

Hi Everyone,

For this question, I'll provide a dummy table to try and explain what I'm looking for. Based on the below table, what I'm looking to do is for TYPE = NEW VEHICLE, check the VIN and if it's a duplicate, flag the oldest MOD.

VIN	MOD	TYPE	FLAG
33476	10	NEW VEHICLE	1
33476	12	NEW VEHICLE	0
38159	16	OLD VEHICLE	0
39138	7	OLD VEHICLE	0
36745	9	NEW VEHICLE	1
36745	10	NEW VEHICLE	0

I saw a way to delete the last.var or flag by checking a single column for duplicates, but this is a bit more complex than I'm familiar with and requires more logic.

Any suggestions would be greatly appreciated.

Thanks

Re: Flag Duplicate Based on Multiple Criteria

ballardw — Thu, 12 Nov 2020 15:50:21 GMT

I suggest that you provide how we identify the "oldest" of anything given that data.

Second provide data in the form of "what I have" and "what I want".

Third, best is to provide the data in the form of a working data step so we can recreate your data and test code.

Re: Flag Duplicate Based on Multiple Criteria

BlayLay — Thu, 12 Nov 2020 16:19:37 GMT

"oldest" would be the lowest MOD (smallest integer) for that particular VIN, if the VIN appears more than once in the dataset.

What I have is the first 3 columns, what I want is the Flag column to be added based on the logic provided above. "0" indicates no flag, 1 indicates a flag.

Re: Flag Duplicate Based on Multiple Criteria

ballardw — Thu, 12 Nov 2020 17:39:10 GMT

This seems to work for your example data:

Proc sort data=have;
   by vin type mod;
run;

data want;
   set have;
   by vin type mod;
   if type = 'NEW VEHICLE' and first.Type then flag=1;
   else flag=0;
run;

However, if you have mix of Old and New and mod values for the same vin no promises.