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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.