proc compare

[HeatherNewton]

proc compare base=common_base compare=common_comp out=common_diff outnoequal;
id account_no;
run;

does anyone know what sql queries can replicate the results of the above sas code? thanks.

[SASKiwi]

By default PROC COMPARE compares all columns of the BASE table with the same-named columns in the COMPARE table so without knowing what columns are in each table it would be very difficult to reproduce all the logic of PROC COMPARE in SQL. Are you comparing SAS tables or tables in other databases?   

[andreas_lds]

The most important questions is: why do you want to replace a simple and easy to read procedure with a sql, requiring at least 10 times the code you have now?

[Kurt_Bremser]

My first answer: don't even think about it.You will have to write pages of SQL code for every single use of PROC COMPARE. Lots of pages.

Stuff like this is (part of) what makes people pay lots of money for using SAS.

If you really must move away from SAS, look at the big picture. Where is the resulting dataset used, and in which way? Often, compare is only used to provide a visual check for someone to look at. What is checked? Answering these questions will provide a clue if the result is important, and how to re-engineer the action in SQL.

[mkeintz]

Proc compare honors record order.  Proc sql need not. 

You might be able to beat sql into submission for this task, but only at the loss of regard from whoever needs to maintain your code.

[HeatherNewton]

sorry I dont mean proc sql from SAS and I mean just common sql.

[Kurt_Bremser]

---

@HeatherNewton wrote:

sorry I dont mean proc sql from SAS and I mean just common sql.

---

Doesn't make a difference. Reimplementing everything that PROC COMPARE does will be an enormous task.

So you must find out what of the many things COMPARE provides is really needed. You may even find that the step was left in the code for no real purpose after testing (most probably if the result dataset is not used anywhere in follow-up code).

[Quentin]

I agree with others.  PROC COMPARE has a lot of good stuff in it, and it would be a lot of work to replicate all of it.

If you want to compare tables in SQL, I would google that.  You'll find links like:

https://www.sqlshack.com/compare-tables-sql-server/

You might want to include your specific flavor of SQL in the search.  It's possible you'll get lucky and find it has implemented a compare tool that is not part of ANSI SQL.

If I were forced to leave SAS, I think PROC COMPARE would be one of the things I miss most of all.  I use it all the time for QC work, both ad-hoc and in production.

[Reeza]

Depending on the system, it may have a built in process to do something similar or you leverage a third party tool (ie SAS) to accomplish this as there isn't a good way to do this easily within SQL. Otherwise you have to manually write the query for each proc compare in most systems, as SAS automatically references each column name and most SQL variants don't do this. 

Some other sources to help support this comment:

https://stackoverflow.com/questions/688537/oracle-diff-how-to-compare-two-tables

https://stackoverflow.com/questions/25398375/oracle-compare-data-between-two-different-table

MySQL

https://ubiq.co/database-blog/compare-two-tables-mysql/

Basically Google "yourDB compare tables" and you'll find many many examples of doing this manually which sucks. 

https://ubiq.co/database-blog/compare-two-tables-mysql/

[SASKiwi]

@HeatherNewton - With all due respect you should really explain what your use case / problem is rather than asking for help with what you think is the right solution. In other posts you have stated that you don't have a SAS license and are migrating all of your SAS data to Datastage. This being a SAS forum, naturally you are going to get SAS-slanted answers from posters.

I'm guessing you want a way to compare data in a non-SAS database but you haven't told us what that database is. There are surely comparison tools available for other databases as pointed out by @Reeza but until you tell us what that is it is difficult to offer further help. 

[Patrick]

@HeatherNewton 

Based on some of your other questions and feedback you've got there as well as here: If you're in a project that aims to replace SAS code with some other language then don't try and just replicate the existing SAS code step by step. 

You need to analyze what the processes written in SAS are intended to do and what outcome they need to deliver and then you need to fully re-design so it's suitable for the target environment and language used. 

If you don't do this then you will get new code on a quality level comparable to translating a text from Chinese to English one word literally at a time.