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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.