Compare the Content, Not the Results, of Procs

Compare the Content, Not the Results, of Procs

If you're using Ruby 1.9, you may be able to use the sourcify gem.

$ irb
> require 'sourcify'
=> true 
> a = proc {@x == "x"}
=> #<Proc:0x9ba4240@(irb):2> 
> b = proc {@x == %{x}}
=> #<Proc:0x9ba23f0@(irb):3> 
> a == b
=> false 
> a.to_source == b.to_source
=> true 
> RUBY_VERSION
=> "1.9.2" 

We also ran into the ParseTree/Ruby 1.9 incompatibility problem at my company.

SQL Server - compare the results of two stored procedures that output multiple tables

Easy option 1: Output the stored procedure results to a text file (one per procedure version) and use a diff tool/editor to make sure they are the same.

Easy option 2: Write the stored procedure results to a table/temp table (per return table per procedure) and write sql to compare the results. Just count the rows in each result table and then do a count of the union (not union all) of both tables. Repeat for each result table.

Comparing output of two versions of a stored procedure using a table

How big is your result set?

One approach might be to declare xml variables

DECLARE @xml1 XML, @xml2 XML;

... and use

SET @xml1=(SELECT ... FOR XML RAW) --without the "INTO SomeTable"! Same with `@xml2` 

At the end you can use

SELECT CASE WHEN CAST(@xml1 AS NVARCHAR(MAX))=CAST(@xml2 AS NVARCHAR(MAX)) THEN ...`

Make sure, that there are no timestamps or other variable content, which would disturb of course...

UPDATE

You might use a declared table variable, which lives as long as your job lives and is not affected by the rollback:

Instead of your SELECT * INTO SomeTable you use INSERT INTO @tableVariable (col1, col2, ...) SELECT col1, col2, ... FROM ....

Just use the existing declaration of your table to create the table variable. No need to type in all columns manually...

A Tool for Comparing Result Sets From Stored Procedures Across Multiple Databases - SQL Server 2008

Not sure if you truly mean "across multiple databases" or "across multiple servers". If its a server you'll need to add linked servers.

The stored proc below will compare the output resultset of 2 stored procedures, or 2 statements. Doesn't need to know the schema of the result set, but the 2 input statements must have identical schemas. It will return 0 rows if the output is the same. This solution uses openrowset command in SQL Server.
Here is some sample usage of the Stored proc

    -- Sample Usage, works for both Queries and Stored Procs    
    DECLARE @SQL_SP1 VARCHAR(MAX)
    DECLARE @SQL_SP2 VARCHAR(MAX)

    -- Compare results of 2 Stored Procs
    SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
    SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
    EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2

    -- Compare just 2 SQL Statements
    SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
    SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
    EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2

The SP requires the following prerequisites because it uses openrowset, which may not be ideal for a production environment.

   -- Code uses openrowset so needs some special permissions
   EXEC sp_configure 'show advanced options', 1
   EXEC sp_configure 'ad hoc distributed queries', 1
   EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE

Here is the code for the stored proc.

==================================================================================
    --== SUMMARY utlCompareStatementResults 
    --==    Compares output of 2 queries or stored procs
    --==    - requires sp_configure 'show advanced options', 1
    --==    - requires sp_configure 'ad hoc distributed queries', 1
    --==    - maybe requires EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
    --==    - requires the RecordSet Output to have Unique ColumnNames (no duplicate columns)
    --==    - requires references in straight SQL to be fully qualified [dbname].[schema].[objects] but not within an SP
    --==    - requires references SP call to be fully qualifed [dbname].[schema].[spname] but not objects with the SP
    --== OUTPUT
    --==    Differences are returned 
    --==    If there is no recordset returned, then theres no differences
    --==    However if you are comparing 2 empty recordsets, it doesn't mean anything
    --== USAGE
    --==   DECLARE @SQL_SP1 VARCHAR(MAX)
    --==   DECLARE @SQL_SP2 VARCHAR(MAX)
    --==   -- Compare just 2 SQL Statements
    --==   SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
    --==   SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
    --==   EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
    --==
    --==   -- Compare results of 2 Stored Procs
    --==   SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
    --==   SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
    --==   EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
    --==================================================================================
    CREATE PROCEDURE utlCompareStatementResults
       @SQL_SP1 VARCHAR(MAX),
       @SQL_SP2 VARCHAR(MAX)
    AS
    BEGIN
        DECLARE @TABLE1 VARCHAR(200)
        DECLARE @TABLE2 VARCHAR(200)
        DECLARE @SQL_OPENROWSET VARCHAR(MAX) 
        DECLARE @CONNECTION VARCHAR(100)

        SET @CONNECTION = 'server='+@@SERVERNAME+';Trusted_Connection=yes'

        SET @SQL_SP1 = REPLACE(@SQL_SP1, '''','''''')
        SET @SQL_SP2 = REPLACE(@SQL_SP2, '''','''''')

        SET @TABLE1 = '#' + SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)
        SET @TABLE2 = '#' + SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)

        SET @SQL_OPENROWSET =
        'SELECT * ' + ' ' +
        'INTO ' + @TABLE1 + ' ' +
        'FROM OPENROWSET(''SQLNCLI'', ' + '''' + @CONNECTION + '''' +
                        ',''' + @SQL_SP1 +'''); ' +
        'SELECT * ' + ' ' +
        'INTO ' + @TABLE2 + ' ' +
        'FROM OPENROWSET(''SQLNCLI'', ' + '''' + @CONNECTION + '''' +
                        ',''' + @SQL_SP2 +'''); ' +
        '(SELECT * FROM ' + @TABLE1 + ' EXCEPT SELECT * FROM ' + @TABLE2 + ') '  +
        ' UNION ALL ' +
        '(SELECT * FROM ' + @TABLE2 + ' EXCEPT SELECT * FROM ' + @TABLE1 + '); ' +
        'DROP TABLE ' + @TABLE1 + '; ' +
        'DROP TABLE ' + @TABLE2 + '; '
        PRINT @SQL_OPENROWSET
        EXEC (@SQL_OPENROWSET)
        PRINT 'DifferenceCount: ' + CONVERT(VARCHAR(100), @@ROWCOUNT)
    END

SAS proc compare unequal value

If your question is about saving the output of Proc compare in another dataset/table, then you can use out option:

proc compare base=old compare=new
out=Out_ds outnoequal outbase outcomp outdif noprint;
id code;
run;

the out_ds will keep the results.

Refer below to keep only the different variable names in output Dataset:

data old;
input code A B C D;
datalines;
101 1 a 1 100
102 2 b 1 104
103 3 c 1 54
104 4 d 1 87
105 5 e 1 201
;
run;

data new;
input code A B C D;
datalines;
101 1 a 1 100
102 2 b 1 13
103 3 c 1 54
104 4 d 2 87
105 5 e 1 12
;
run;

proc sort data=old; by code; run;
proc sort data=new; by code; run;

/*suppresses the writing of an observation to the output data set when all values in the observation are judged equal. 
  In addition, in observations containing values for some variables judged equal and others judged unequal, 
  the OUTNOEQUAL option uses the special missing value ".E" to represent differences and percent differences for variables judged equal.*/
proc compare base=old compare=new
out=Out_ds outnoequal;
id code;
run;

/*Get all the variable names from output dataset which you are comparing*/
proc sql ;
  select strip(name)
    into :vnames
    separated by " "
    from dictionary.columns
   where libname="WORK" and
         upcase(memname)="OUT_DS" and
         upcase(strip(name)) not in('_TYPE_','_OBS_','CODE')
  ;
quit;

/*This macro will loop through every value and will keep only those variables in keep_vars, which have unequal values*/
options merror mprint nosymbolgen mlogic;
%macro keepv(varlist);
data out_ds1;
length keep_vars $100.;
     set out_ds;
     retain keep_vars;
     %let var_c=%sysfunc(countw(&varlist));
     %do i=1 %to &var_c;
     %let var_t=%sysfunc(scan(&varlist,&i));
        if strip(&var_t) ne 'E' and findc(keep_vars,"&var_t") ne 1 then 
        do;
            keep_vars=catx(",",keep_vars,"&var_t");
        end;
     %end;
    run;
%mend keepv;

%keepv(&vnames);

/*keep the last obs -  keep_vars have the required variable names only*/
data out_ds_final;
if 0 then set out_ds1 nobs=nobs end=eof;
set out_ds1 point=nobs;
output;
stop;
keep keep_vars;
run;

proc print data=out_ds_final; run;

Why does finding the average in a proc means and a proc sql step yield different results according to a proc compare?

Differences on the order of E-14 are to be expected from calculations done on floating point values (as SAS numbers are). This isn't particularly related to the different PROCs; all it would take would be summing values in a different order to produce an error along those lines. Even two different runs in PROC SQL could generate differences of this magnitude, if rows end up processed differently (due to multithreading, for example).

PROC COMPARE runs should generally be done with the FUZZ option, unless you are comparing numbers with very small magnitudes. This should usually be part of standard practice, unless you specifically want to see this kind of difference (meaning, unless you want to verify two files are the same file, and not just the same values).

Proc compare - why am I getting tiny differences when the values are exactly the same?

The numerical precission on the computer is not perfect, that is why proc compare can report tiny differences on "identical" numbers.

There are a couple of options you can use to ignore these small differences (taken from documentation):
METHOD=ABSOLUTE | EXACT | PERCENT | RELATIVE <(delta)>
specifies the method for judging the equality of numeric values. The constant (delta) is a number between 0 and 1 that specifies a value to add to the denominator when calculating the equality measure. By default, is 0.
Unless you use the CRITERION= option, the default method is EXACT. If you use the CRITERION= option, then the default method is RELATIVE( ), where (phi) is a small number that depends on the numerical precision of the computer on which SAS is running and on the value of CRITERION=.

FUZZ=number
alters the values comparison results for numbers less than number. PROC COMPARE prints:

0 for any variable value that is less than number.

a blank for difference or percent difference if it is less than number

0 for any summary statistic that is less than number.

Default 0
Range: 0 - 1
Tip: A report that contains many trivial differences is easier to read in this form.
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146741.htm

---

Related Topics