Compare file specification table to test data

[bmerry1]

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.

[Linlin · Accepted solution]

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;

[bmerry1]

Thank you Linlin.  Worked perfectly.  I appreciate your taking the time to respond.

Best,

Brian