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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 711 views
  • 1 like
  • 2 in conversation