Proc compare - comparing variables in two datasets that have different sizes and different variable placement

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Proc compare - comparing variables in two datasets that have different sizes and different variable placement

So, I have a significant problem with proc compare. I have two datasets with the two columns. One column lists table names and the other one - names of variables which correspond to table names from the first column. I want compare values of one of them based on the values of first column. I somewhat made it work but the thing is that these datasets have different sizes due to additional values in one of them. Which means that some new variable was added in the middle of a dataset (new variable was added to a table). Unfortunately, proc compare compares values from two datasets horizontally and checks them against each other for values, so in my case it looks like this:

    ds 1 | ds 2

    cost | box_nr

    other | cost_total

As you can see, a new value `box_nr` was added to the second dataset that appears above the value  that I want it to compare variable `cost` to (`cost_total`). So I would like to know if it's possible to compare values (check for differences in character sequence) that have at least minimal similarity - for example 3 letters (cos) or if it's possible to just put values like `box_nr` at the end suggesting that they don't appear in a certain dataset.

My code:

    PROC Compare base=USERSPDS.MIzew compare=USERSPDS.MIwew
        out=USERSPDS.result outbase outcomp outdif noprint;
        id 'TABLE  HD'n;
        where ;
    run;

    proc print data=USERSPDS.result noobs;
       by 'TABLE  HD'n;
       id 'TTABLE  HD'n;
       title 'COMPARISON:';
    run;


Accepted Solutions
Solution
‎09-17-2014 05:30 AM
Super User
Super User
Posts: 7,720

Re: Proc compare - comparing variables in two datasets that have different sizes and different variable placement

Your better off using proc sql, joining the two tables - assuming columns are DATASET, VAR_NAME:

proc sql;

     create table MATCHES as    

     select     COALESCE(A.DATASET,B.DATASET) as DATASET,

                   COALESCE(A.VAR_NAME,B.VAR_NAME) as VAR_NAME,

                    case when A.DATASET="" or A.VAR_NAME="" then "N" else "Y" as IN_BASE,

                    case when B.DATASET="" or B.VAR_NAME="" then "N" else "Y" as IN_COMP,

     from       BASE_DATASET A

     full join   COMP_DATASET B

     on           A.DATASET=B.DATASET

     and          A.VAR_NAME=B.VARNAME;

quit;

This will give a full list of all dataset/varname combinations and a flag if its present in either.

View solution in original post


All Replies
Solution
‎09-17-2014 05:30 AM
Super User
Super User
Posts: 7,720

Re: Proc compare - comparing variables in two datasets that have different sizes and different variable placement

Your better off using proc sql, joining the two tables - assuming columns are DATASET, VAR_NAME:

proc sql;

     create table MATCHES as    

     select     COALESCE(A.DATASET,B.DATASET) as DATASET,

                   COALESCE(A.VAR_NAME,B.VAR_NAME) as VAR_NAME,

                    case when A.DATASET="" or A.VAR_NAME="" then "N" else "Y" as IN_BASE,

                    case when B.DATASET="" or B.VAR_NAME="" then "N" else "Y" as IN_COMP,

     from       BASE_DATASET A

     full join   COMP_DATASET B

     on           A.DATASET=B.DATASET

     and          A.VAR_NAME=B.VARNAME;

quit;

This will give a full list of all dataset/varname combinations and a flag if its present in either.

Contributor
Posts: 27

Re: Proc compare - comparing variables in two datasets that have different sizes and different variable placement

Thank you.

Contributor
Posts: 27

Re: Proc compare - comparing variables in two datasets that have different sizes and different variable placement

And what if I don't want to see values that are present in both of these datasets?

Super User
Super User
Posts: 7,720

Re: Proc compare - comparing variables in two datasets that have different sizes and different variable placement

Change:

     and          A.VAR_NAME=B.VARNAME;

quit;

To:

     and          A.VAR_NAME=B.VARNAME

     where     IN_BASE="N" or IN_COMP="Y";

/* note may need to replace with where CALCULATED IN_BASE="Y"... */

quit;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 306 views
  • 0 likes
  • 2 in conversation