topic Re: Proc compare discrepancy for alignment in New SAS User

Proc compare discrepancy for alignment

yogita_patel — Wed, 22 Jun 2022 20:56:48 GMT

Hi,

I am running a report for proc compare. I check manually all the values are exactly matching but still proc compare is showing discrepancy for 0 (zero) value only.

and I use compress, strip, left, all type of functions to remove extra spaces so that I will get 100% match for proc compare.

But still there is leading blanks for record label. I am having same issue for other table also. compare dataset is generated by me and base is by production programmer

can somebody help?

Re: Proc compare discrepancy for alignment

ballardw — Wed, 22 Jun 2022 21:09:14 GMT

Data. Data. Data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Code used for the attempt to clean up your values and then compare.

Re: Proc compare discrepancy for alignment

SASKiwi — Wed, 22 Jun 2022 21:15:36 GMT

Help you do what? PROC COMPARE is accurately reporting that you have left aligned your character columns but your production programmer hasn't. First decide how you want to align your data then ensure the production version and your version apply the same SAS statements to do that.

Re: Proc compare discrepancy for alignment

yogita_patel — Wed, 22 Jun 2022 21:29:21 GMT

I use compress, strip, left function to align data to left but for production strip or left is not working. only compress is working.

Re: Proc compare discrepancy for alignment

SASKiwi — Wed, 22 Jun 2022 21:35:45 GMT

If you use exactly the same logic as your production program and exactly the same input dataset, you should get the same output data. If this is not the case then I can only assume the input data is different.

Re: Proc compare discrepancy for alignment

yogita_patel — Wed, 22 Jun 2022 21:39:10 GMT

I checked manually all values and labels are exactly same.

Re: Proc compare discrepancy for alignment

data_null__ — Wed, 22 Jun 2022 22:23:37 GMT

If you can't remove leading blanks it suggest there are no leading blanks. Perhaps the non-breaking space. Look at your data with $HEX format.

Better yet, show us the data using $HEX format.

Re: Proc compare discrepancy for alignment

SASKiwi — Wed, 22 Jun 2022 22:19:33 GMT

Does that include data alignment? If values are the same but alignment is different then character data values are NOT identical. For example 'ABC' (no blank spaces) is not the same as ' ABC' (one leading blank space).

Re: Proc compare discrepancy for alignment

ballardw — Wed, 22 Jun 2022 22:20:54 GMT

@yogita_patel wrote:
I checked manually all values and labels are exactly same.

We routinely deal with people on this forum that claim there data is a Date value (which means some specific ranges of numeric values) and after 20 or more questions the values turn out to be character. Or a format is applied that will display values for print that "manually are exactly the same" but in reality are not. There are a number of other things that "manual" comparison, especially if it means looking at printed output and thinking it is identical. Even worse: exported to Excel and then looked at as Excel is known to apply all sorts of rules to things and change values.

Proc Compare is old. What that means is that it has been tested and used for well over 30 years. It does not make up differences to report. You need to provide some concrete example of the data processed that you claim is identical and the code used to compare it.

In this case I think actual sas7bdat files may be needed. Subset a few records and variable that demonstrate the difference. Provide the starting data and the code used to prepare them for Proc Compare and the Compare code.

By the way, when you say LABEL are you referring to SAS variable labels? SAS data set Labels? or the values of one of your variables in a data set?

Re: Proc compare discrepancy for alignment

yogita_patel — Thu, 23 Jun 2022 19:01:17 GMT

can i remove or add $HEX format? as I dont see code for production programmer. I have only final dataset.

Re: Proc compare discrepancy for alignment

data_null__ — Fri, 24 Jun 2022 13:47:42 GMT

You can display your data (proc print) and assign format.

proc print;
   format _char_ $hex.;
   run;

Re: Proc compare discrepancy for alignment

Tom — Fri, 24 Jun 2022 14:04:50 GMT

For COL2 this simple test should tell you want is happening.

proc freq data=BASE;
  tables col2 ;
 format col2 $hex4.;
run;
proc freq data=COMPARE;
  tables col2 ;
 format col2 $hex4.;
run;

So it looks like the dataset on the right has a lot of observations with leading spaces, which will display as '2020' using $HEX4. format. If instead you see a lot of values of '0909' or 'A0A0' then you know that the those characters are NOT spaces.

If COL3 is also character then do the same. If it is numeric try using HEX16. format instead. If the value is actually zero it will print as 16 zeros. If it is some very small number it will print as something else.

1637  data test;
1638    do col3=0,1E-20;
1639      put col3=4. col3 hex16.;
1640    end;
1641  run;

col3=0 0000000000000000
col3=0 3BC79CA10C924223