topic Re: Proc Compare Large dataset with many columns in New SAS User

Proc Compare Large dataset with many columns

Binxie — Mon, 29 Jul 2019 14:21:30 GMT

Hello

I have a two extra large data sets (>9Million records) that I'm am trying to compare. The data sets have over 300 columns in them.

I was able to run a proc compare that outputs the differences only by ID, however, because of the size of the data set and the restrictions on exporting at my company, I cannot get the data out to analyze on a record by record basis which columns are not matching.

Is there an easy way in SAS that I can only show the variables (by the ID key) that are different? Any other suggestions on how I can view only the differences in the individual columns?

Thanks

Re: Proc Compare Large dataset with many columns

Reeza — Mon, 29 Jul 2019 15:09:02 GMT

Why can't you get the output? Have you tried suppressing the printed output and generating a data set to view instead?

Re: Proc Compare Large dataset with many columns

Binxie — Mon, 29 Jul 2019 15:27:32 GMT

I can view the output fine. I just need the ability to identify the differences easily. Currently there are over 400,000 rows and in the 350+ columns there could be differences. I'm just trying to identify, by the ID which records in which columns have differences.

If I could export, it would be easier to analyze, but I can't so I'm trying to figure out a way to just output by ID the column where the dif=XXX.

Re: Proc Compare Large dataset with many columns

Reeza — Mon, 29 Jul 2019 15:31:35 GMT

Are you outputting your results to a data set using the OUT option? If not, that's likely what you want to do, since you can then filter that as desired. The OUT options are on the PROC COMPARE statement. If you don't know how, please show your current code and we can show you how to modify it.

Re: Proc Compare Large dataset with many columns

Binxie — Mon, 29 Jul 2019 15:38:21 GMT

Yes, I'm outputting it to a data set. The problem is because there is over 400,000 records and 350+ columns, I don't know the best way to find the differences. How do I easily identify, by the Primary key, which columns have the differences without scrolling thru 400,000 rows and 350 columns.

Re: Proc Compare Large dataset with many columns

Reeza — Mon, 29 Jul 2019 15:40:42 GMT

Have you tried using the OUTDIF or OUTNOEQUAL options to limit the output to records with differences?

Re: Proc Compare Large dataset with many columns

Reeza — Mon, 29 Jul 2019 15:41:11 GMT

Did you use the METHOD/FUZZ option so that minute differences are suppressed?

Re: Proc Compare Large dataset with many columns

Binxie — Mon, 29 Jul 2019 15:45:18 GMT

These are actual differences. The two data sets need to be identical. It's a new view that was created from an old one, and we are testing that the landed data is matching as the business expects it to.

Re: Proc Compare Large dataset with many columns

Binxie — Mon, 29 Jul 2019 15:56:55 GMT

Yes, I have outdif and outnoequal in my query. There are actually 400,000 difs in the data.

proc compare base=recs_a compare=recs_b

out=result outnoequal outbase outcomp outdif

noprint;

id POLICY_NUMBER TERM_NUMBER POLICY_RISK_IDENTIFIER EFFECTIVE_FROM EFFECTIVE_TO;

run;

Re: Proc Compare Large dataset with many columns

Reeza — Mon, 29 Jul 2019 15:58:03 GMT

Try just having the outnoequal, I think outnoequal and outdif are the opposite of each other.