Proc Compare, weird results?

[ChuckMoore]

I have used proc compare off an on for the last year or two.

I am getting some strange results that I/we don't understand:

                                                         Variables Summary

                                 Number of Variables in Common: 1349.
                                 Number of Variables in PRDTABLE.CHD0 but not in TESTVIEW.CHD0: -1.
                                 Number of Variables in TESTVIEW.CHD0 but not in PRDTABLE.CHD0: 250.
                                 Number of Variables with Differing Attributes: 275.

What's with the -1 ?

Also, a colleague, when testing the same objects (he used different librefs), gets no output what-so-ever.

He has some compare options set =

PROC COMPARE base    = testchd.chd0

             compare = prodchd.chd0 brief listvar maxprint=(5,5000)

             listall

             out=result outnoequal outbase outcomp outdif;

             id  acct_id;

        var

          ACCOUNT; 

run;

His log has the following message:

NOTE: The data set WORK.RESULT has 0 observations and 0 variables.

And then he gets nothing in the list file.

Any clues from anybody?

[Tom]

How many variables in each of the datasets?  Is it possible PROC COMPARE has a limit on the number of variables it can compare?

What format are the datasets?  Are they real datasets or views into a database?

What happens if you create copies of the data in WORK library and compare the copies?

[ChuckMoore]

Tom,

Yes, we are dealing with a view.

From the provided output, there are 1349 variables in common, and the "compare=" set has an additional 250, yielding 1599

But, proc contents:

                Data Set Name        PRDTABLE.CHD0                          Observations         7162415

                Member Type          DATA                                             Variables              1348

                Engine                    V9                                                 Indexes                3

                Created              Friday, January 06, 2012 12:50:51 PM             Observation Length     8373

                Last Modified      Friday, January 06, 2012 12:50:51 PM             Deleted Observations   0

                Protection                                                            Compressed             BINARY

                Data Set Type                                                         Reuse Space            NO

                Label                                                                 Point to Observations  YES

                Data Representation  HP_UX_64, RS_6000_AIX_64, SOLARIS_64, HP_IA64    Sorted                 YES

                Encoding             latin1  Western (ISO)

                Data Set Name        NEWTABLE.CHD0                                    Observations           7162415

                Member Type          DATA                                             Variables              1397

                Engine               V9                                               Indexes                3

                Created              Thu, Feb 02, 2012 10:10:59 PM                    Observation Length     8831

                Last Modified        Thu, Feb 02, 2012 10:10:59 PM                    Deleted Observations   0

                Protection                                                            Compressed             BINARY

                Data Set Type                                                         Reuse Space            NO

                Label                                                                 Point to Observations  YES

                Data Representation  HP_UX_64, RS_6000_AIX_64, SOLARIS_64, HP_IA64    Sorted                 YES

                Encoding             latin1  Western (ISO)

                     Data Set Name        TESTVIEW.CHD0                            Observations          .

                     Member Type          VIEW                                     Variables             1599

                     Engine               SQLVIEW                                  Indexes               0

                     Created              Friday, February 03, 2012 07:13:19 AM    Observation Length    10386

                     Last Modified        Friday, February 03, 2012 07:13:19 AM    Deleted Observations  0

                     Protection                                                    Compressed            NO

                     Data Set Type                                                 Sorted                YES

                     Label                Observations:      7162415

                     Data Representation  Default

                     Encoding             Default

But, since both myself and my colleagure are running proc compares against the original production table, and the view to the new production table, why would I get some results, and he get no results?

[ChuckMoore]

Found out why the -1.

The view is a proc sql view.

It contains 250 aliases, which have "old names" for columns that have "new names", without duplicating the values.

One alias is a duplicate of an already existing variable, contained in the "select * ... " portion of the view definition.

But, does that explain why my colleague got 0 results? with no listing?

[ChuckMoore]

Tom,

The result of creating a work dataset from the view yielded a data set with 1598 variables.

Obvious, the proc compare of table versus table worked as expected, especially without the duplicate variable name in the view.