Hi and thank you in advance for any assistance. I was hoping to get some information on what may be a useful/best method using SAS to compare a reference data file that contains a specific set of values and labels to a production data file that utilizes the codes. For example:
Reference data:
CARMODEL CARTYPE
Camaro sport
Landcruiser suv
Explorer suv
Ranger truck
Production data file:
DRIVER CARMODEL CARTYPE
Toonces Camero sport
Danny Explorer suv
Henry Camero sport
Winston Landcruiser truck
I would like to be able to compare the production file against the reference data file to check for programming/entry errors--for example 'Camero' instead of 'Camaro' and Landcruiser incorrectly associated with truck instead of suv. I had been exploring using PROC COMPARE, but this does not seem to be the appropriate method for performing this type of one to many comparison. I would really appreciate and guidance, suggestions, or approaches anyone is willing to share. Thank you again for your time and assistance.
not tested code:
proc sql;
select * from production
where catx('-',upcase(carmodel),upcase(cartype))
not in (select catx('-',upcase(carmodel),upcase(cartype)) from reference);
quit;
not tested code:
proc sql;
select * from production
where catx('-',upcase(carmodel),upcase(cartype))
not in (select catx('-',upcase(carmodel),upcase(cartype)) from reference);
quit;
Thank you Linlin. Worked perfectly. I appreciate your taking the time to respond.
Best,
Brian
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.