topic Proc Compare, weird results? in SAS Procedures

Proc Compare, weird results?

ChuckMoore — Mon, 06 Feb 2012 16:37:42 GMT

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?

Proc Compare, weird results?

Tom — Mon, 06 Feb 2012 16:56:03 GMT

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?

Proc Compare, weird results?

ChuckMoore — Mon, 06 Feb 2012 17:08:56 GMT

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?

Proc Compare, weird results?

ChuckMoore — Mon, 06 Feb 2012 17:28:10 GMT

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?

Proc Compare, weird results?

ChuckMoore — Mon, 06 Feb 2012 17:30:22 GMT

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.