SAS Merge Equivalent Object Model SQL

The SAS Merge SQL Equivalent Object model simulates the SAS MERGE statement using SQL. It generates one SQL statement that is equivalent to the SAS merge behaviour. The object model consists of six classes written in Python with Visual Studio 2017 and tested in PostgreSQL. But it is standard SQL so should work everywhere. The model looks as follows:

When you feed the returning SQL statement to a SQL data provider you can then iterate through the rows to do something with the data and you can fully emulate, or come close, the SAS data step technique.

The SAS overlay aspect is not implemented; column names with the same names are not overwritten. Also the MERGE equivalent without a BY statement is not supported. Also the position of a column in the SELECT clause is rather fixed.

SAS overlay means that same name columns are over written from right to left. The most right table in the MERGE statements overwrites columns in the left table having the same name. This means the order of appearance in the SAS MERGE statement is relevant, just as the ordering of a table.

When you do not want to iterate through the result data set, can also use the returning SQL query as in inner query or use the WITH clause.

Supporting pointers per table (the IN option) are available telling you when there was a match or not. The WHERE clause can be used to keep only the rows you want. The WITH clause gives you the most power to further manipulate the data.

  SELECT Tx.<whatever>
  FROM (
    SELECT <SAS_MERGE_QUERY>
  ) Tx
  WHERE <whatever>
      

  WITH Tx as (
    <SAS_MERGE_QUERY>
  ),
  <any other query>
  SELECT Tx.<whatever>
  FROM Tx
  WHERE <whatever>

The SQL equivalent has no limits. The limits are the data provider and target database.

Dowload SAS Merge SQL Equivalent Object Model Description.

Download the Python source code.