proc compare - the differences between 2 libraries and output the diffs into ONE Excel spreadsheet

[sukiviz]

hi all

Here's a task and a half !

Im comparing data sets in 2 different libraries  , and then wish to output the differences like extra obs, extra vars, new values, dataset exist or not into  ONE spreadsheet    ; there about 50 data sets.

Ive got  loads of pdfs already done produced ODS , but the task I want to achieve involves manually cutting and pasting all the differences into one separate spreadsheet.

Is there a way of using proc compare so that it inserts all the differences (Stated above into 1 concise spreadsheet) please?

would be indebted?

thanks in advance

Suki

[ballardw]

I'm afraid that your "new values" is going to require a definition of "new". That tends to imply an order of comparison and I likely going to be the hardest to get.

You can get some of this information, nob and existence from SASHELP.VTABLE or the Proc SQL dictionary.tables.

Example:

proc report data=sashelp.vtable;
   where libname in ('LIBNAME1' 'LIBNAME2') and memtype='DATA';
   column memname libname,(nobs nvar);
   define memname /group;
   define libname/across;
run;

Will create a table with the data set name as the row and within columns for each library have columns that are the number of observations and number of variable, missing obs and vars indicate the data set is not in the library. Up to you to decide what "extra" would be. Note the Libname and Memtype values are stored as upper case and need to be that way in the where clause.

You can get similar information on variables from the SASHELP.VCOLUMN or Proc SQL dictionary.columns.

proc format library=work;
value 0-high='X' .=' ';
run;

proc tabulate data=sashelp.vcolumn;
   where libname in ('LIBNAME1' 'LIBNAME2') and memtype='DATA';
   class memname name type/missing;
   table name*memname*type,
         libname*n=' '*f=mark.
         ;
run;

Will create table with the variable name, data set name, variable type as row header and the library as the column header. The format places an X where the values appear.

This will show all the data sets that a like named variable will appear.

No easy/short way to get values. You have to summarize every variable in every set to get distinct values and then compare the paired results.