DATA Step, Macro, Functions and more

Compare file specification table to test data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Compare file specification table to test data

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.


Accepted Solutions
Solution
‎10-04-2012 04:17 PM
Super Contributor
Posts: 1,636

Re: Compare file specification table to test data

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;

View solution in original post


All Replies
Solution
‎10-04-2012 04:17 PM
Super Contributor
Posts: 1,636

Re: Compare file specification table to test data

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;

Occasional Contributor
Posts: 13

Re: Compare file specification table to test data

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

Best,

Brian

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 169 views
  • 1 like
  • 2 in conversation