Proc Compare (EG)

[asdf_sas]

Hello all,

i have a question about proc compare. i know the eg section isnt the right one but i would like to put it here because

i dont know if there are any differences between z/os /windows/unix in sas.... well it doesnt matter for my question.

if i'm absolutely wrong here please move this entry.

now to my question:

i would like to know how many rows are equal and unequal... well thats not the problem.

the problem is how proc compare visualizes the results.

i used a simple proc compare:

proc compare base=work.old compare=work.new outall out=work.equal;

run;

and i get something like this

Type                  OBS        Timestamp

BASE                  1    12/19/2013 06:00:04.010

COMPARE           1    12/19/2013 06:00:04.010

DIF                      1    ..............................................

PERCENT            1    ..............................................

i dont like the way proc compare visualizes the results. i think the ".................." for equal datasets are uncomely to handle

and very error-prone. is there a way to get the results shown more comfortably? Any other possibility to visualize that there are differences or no differences?

Something like this:

Type                  OBS        Timestamp

BASE                  1    12/19/2013 06:00:04.010

COMPARE           1    12/19/2013 06:00:04.010

DIF                      1    none / 0 / equal

PERCENT            1    100%

Evenn if there are differences the visualization is horrible:

BASE               1    12/19/2013 06:00:04.010

COMPARE        1    12/19/2013 06:00:03.947

DIF                   1    ..................X.XXX.......................

PERCENT         1    ..................X.XXX.......................

I checked the proc compare options offered by sas: Base SAS(R) 9.2 Procedures Guide

but i cant find any useful....

thanks for the help and kind regards,

s

[RW9 · Accepted solution]

You could post process the output dataset into a nicer format.  Whilst the proc compare output is ok for most cases, it can be beneficial to do at least part of it yourself.  Recently for instance I had two RTF files to be compared, there were know issues, so a proc compare then post-processing the output from that was the way to go.  You could produce a nice report as well.  As far as I know there are really only options on what gets output, no real change to operation.

Also, depending on your id variables, you could do the merge yourself manually and then apply any kind of comparison logic yourself:

proc sql;

     create table ERRORS as

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

                    A.DATE as DATE_A,

                    B.DATE as DATE_B,

                    case     when A.DATE is not null and B.DATE is null then "B is missing date"

                                 when A.DATE <= B.DATE then "B date is not before A date"

                                 ....

                                 else "" as ERROR

     from       BASE_DATASET A

     full join   COMP_DATASET B

     on          A.ID=B.ID;

quit;

[asdf_sas]

yeah thanks!

hahah should've got to this solution by myself >.<!