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

Are you trying to generate code or run code using the macro? Because your code in its current form just writes the PROC SQL code to the log file. — SAS2Python
Hey @PythonRSAS, I'm trying to build the code in steps: step 1 was just to generate the code for the update portion so I could inspect and ensure the code is generating properly. Step 2 would be to add in the code for the insert portion, then step 3 would be executing the code. However, I got stuck at step 1! — Rookatu
OK understood. Can you post the full output that you trying to run that was produced by the macro. Without having the values for various macro variables it is difficult to say where those errors you mention are coming from — SAS2Python
Since you seem to be using PUT statements to generate code why not use a dataset to drive the process instead of macro variables. That should be much easier to debug than macro code. Once you have the code file generated use %include to execute it. — Tom
Is the question how to generate the syntax you show? Or how to execute the syntax you show? If the former then show the source of the information that needs to vary. If the later then show what you have tried. — Tom

Richard Richard · Accepted Answer · 2019-08-20T14:40:06

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;

Upsert into SQL Server from SAS

1 Answers