BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bmerry1
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

2 REPLIES 2
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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

Best,

Brian

SAS Innovate 2025: Call for Content

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 16. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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