Upsert into SQL Server from SAS

Question

I've got several datasets which need to be upserted into a SQL server database from SAS (my environment uses SAS DI 4.9).

The default table loader transformation that comes packaged with SAS DI offers an Update/Insert load style, with options to match by SQL set, column, or index. None of these works for me, instead throwing the error

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]A cursor with the name 'SQL_CUR608F0C44282B0000' does not exist.

This SAS note indicates that this issue may be related to the version of the DataDirect driver and that there are workarounds, but the workaround for the version of SAS running in my environment causes poor read performance (which isn't acceptable for my needs). The environment is administered by IT.

What I'd like to do is leverage SAS DI's custom transformation abilities to build something that works the way the Table Loader transformation should have for users with my setup. This would entail some SQL pass-through which uses an update + insert approach, but where the column and table names are programmatically determined from the inputs and outputs to the transformation, and the match columns are specified by the user as with the default transformation.

This requires some serious macro magic.

Here's what I've tried for just the update portion (with anonymized info in [ square brackets ]):

%let conn = %str([my libname]); 
%let where_clause =  &match_col0 = &match_col0;

%macro custom_upsert;

  data _null_;
      put 'proc sql;';
      put 'connect to ODBC(&conn);';

      put "execute(update &_OUTPUT";
      %do i=1 %to &_OUTPUT_col_count;
          put '&&_OUTPUT_col_&i_name = &&_OUTPUT_col_&i_name';
      %end;
      put 'from &_OUTPUT join &_INPUT on';
      put 'where &where_clause';
      put ') by ODBC;';
      put 'quit;';
  run;
%mend;

%custom_upsert;

But this is failing with errors about unbalanced quotation marks and the quoted string exceeding 262 characters.

How can I get this working as intended?

EDIT

Here is the SQL server code that I am ultimately trying to get at with my SAS code, with the major difference here being that the SQL code references two SQL server tables but in reality I'm trying to update from a SAS table:

 begin
    update trgt_tbl
    set  col1 = col1
        , ...
        ,coln = coln
    from trgt_tbl
    join upd_tbl
    on trgt_tbl.match_col = upd_tbl.match_col;

    insert into trgt_tbl
    select * from
    (select 
      col1
     , ...
     ,coln
     from upd_tbl) as temp
    where not exists 
     (select 1 from trgt_tbl
      where match_col = temp.match_col);
end

Answer 1

The macro could generate the SQL code directly, not output the desired code to log (which put will do). However, you could also put to a file that will be submitted via %include. The code gen into the file still has macro resolution references (&&) due to the single quoted put. Thus, those macro variables to be resolved must be existent in the scope at the %include time.

%macro myupsert;
  filename myupsert 'c:\temp\passthrough-upsert.sas';
  data _null_;
    file myupsert;
    …
    /* same puts */
    … 
  run;
  %include myupsert;
  filename myupsert;
%mend;

%myupsert;