topic Re: PROC Compare Output - Cater Rounded values on Base dataset in SAS Programming

PROC Compare Output - Cater Rounded values on Base dataset

AieuYuhara — Mon, 06 Nov 2017 02:50:36 GMT

Hi all,

I performed PROC COMPARE to a dataset, as purpose of UAT, I have to check the accuracy of the data. In the first stage, I include the data that we have and saw A LOT of discrepancies. Hence, I tried to used same dataset which been used to upload into the server, then performed PROC COMPARE, I found out, IT STILL HAVE UNEQUAL VALUES.

Through eyeball checking, the base and compare data set, does HAVE SAME VALUES. But when performing PROC COMPARE, I realized that the variables in Base data set has been rounded to a number which I dont know about.

As an example:

Base: 0.38459348 ------ Compare: 0.38459348

Output >> 0.3846 base ---- 0.38459348 >> Different: -0.00000652

How can I cater this problem? I want the base dataset to be read as it is, without rounding up any values on the Base dataset

Kindly help. Really appreciate your help!

Re: PROC Compare Output - Cater Rounded values on Base dataset

ChrisNZ — Mon, 06 Nov 2017 04:08:15 GMT

Proc compare does not round. The data is different.

One way to do what you want may be to compare within say a thousandth.

Add options: method=absolute criterion=0.001

Re: PROC Compare Output - Cater Rounded values on Base dataset

AieuYuhara — Mon, 06 Nov 2017 05:32:09 GMT

Tried before, and still couldn't capture that the data is actually the same. The problem only appear to the numbers in the Base dataset, which is exactly the same as the one I uploaded to compare with. Logically, it should be an equal values for all fields.

Plus, I noticed that in the output, it only have 4 decimal points max whereas my data has 8 decimal points. Any other way to cater this difference? Like letting the base data set read the way it is, which is 8 decimal points instead of just 4..?

Re: PROC Compare Output - Cater Rounded values on Base dataset

ChrisNZ — Mon, 06 Nov 2017 06:12:16 GMT

1. Did you try these 2 options? They will solve the issue with the data example you showed.
2. Are the formats the same?
3. Just checking. They are numeric variables right?

Re: PROC Compare Output - Cater Rounded values on Base dataset

AieuYuhara — Mon, 06 Nov 2017 06:38:22 GMT

1. Yes. Below is the example output I got:

Base ABC	Compare ABC	Diff.
0.254	0.25396771	-3.229E-05
0.1234	0.12340589	5.89E-05

As noticed, the base ABC value is rounded. From eyeball checking, the base table ABC have exactly same value and same format as Compare ABC. But when we use to compare between both table, it only compares 4 decimal places (from its actual 8 decimal points) vs 8 decimal values (as in the compare table). It didnt round up any value in compare table, but it does round up the value in the base table.

2. Yes. Format is the same. Numeric BEST12.

3. Yes. Numeric Variable.

Re: PROC Compare Output - Cater Rounded values on Base dataset

ChrisNZ — Mon, 06 Nov 2017 09:26:20 GMT

You are doing something wrong. The comparison works fine here.

data T1;
  X=0.1234;
data T2;
  X=0.12340589;
proc compare data=T1 compare=T2 method=absolute criterion=0.001;
run;

                                                                                               
                                   Values Comparison Summary                                   
                                                                                               
                Number of Variables Compared with All Observations Equal: 1.                   
                Number of Variables Compared with Some Observations Unequal: 0.                
                Total Number of Values which Compare Unequal: 0.                               
                Total Number of Values not EXACTLY Equal: 1.                                   
                Maximum Difference: 0.00000589.