Best way to do SQL Update from another table

[Patrick · Accepted solution]

@WaiLoon 

Below some fully functional sample code that hopefully will show you the way.

/* options to write to the SAS log more info how SAS interacts with the DB */
options sastrace=',,,d' sastraceloc=saslog nostsuffix;

/* define common libname options */
%let pg_conn_common=
  DATABASE=<pg database name>  
  SERVER="<pg server name>" 
  PORT=<port used by SAS to connect to PG> 
  authdomain=<authdomain as defined in SAS metadata - alternatively use user/password> 
  DBMAX_TEXT=32000  
  DIRECT_EXE=DELETE  
  DBCLIENT_MAX_BYTES=1 
  ;

/* libname for permanent pg table to be updated           */
/* - GLOBAL connection required to use pg temporar tables */
LIBNAME pg_perm POSTGRES  
  CONNECTION=GLOBAL  
  SCHEMA="<pg schema name of permanent master table>"
  &pg_conn_common
  ;

/* libname for temporary pg table with updates                     */
/* - pg temporar table don't have a schema                         */
/* - pg temporary tables only exist within the scope of a session  */
/*   and are only accessible within the scope of a session         */
/*   (similar to SAS Work tables)                                  */                           
LIBNAME pg_temp POSTGRES  
  CONNECTION=GLOBAL  
  DBMSTEMP=YES  
  &pg_conn_common 
  ;

/* macro to drop tables if they exist */
%macro drop_tbl_if_exists(lref_tbl);
  %if %sysfunc(exist(&lref_tbl)) %then
    %do;
      proc sql;
        drop table &lref_tbl;
      quit;
    %end;
%mend;

/* drop tables to make sample code re-runnable */
/* - NB: PG temporary tables only exists during a session */
%drop_tbl_if_exists(pg_perm.class);
%drop_tbl_if_exists(pg_temp.class_updt);

/* load SAS master table into pg permanent table         */
/* - SAS will create the pg table if it doesn't exist    */
/*   inspect SAS log more detail                         */
proc append base=pg_perm.class data=sashelp.class;
run;quit;

/* create table for updates in SAS Work */
data work.class_updt;
  set sashelp.class;
  if name='Alice' then 
    do;
      age=99;
      output;
    end;
  if name in ('Alfred','Jane') then
    do;
      weight=-10;
      output;
    end;
run;

/* load SAS update table into pg temp table              */
/* - SAS will create the pg table if it doesn't exist    */
/*   inspect SAS log more detail                         */

proc append base=pg_temp.class_updt data=work.class_updt;
run;quit;

/* print pg temp table */
proc sql;
  select *
  from pg_temp.class_updt
  order by name;
quit;

/* update master table with temp table                             */
/* - using explicit SQL pass-through                               */
/* - executes fully in-database; SAS just sends the code to the DB */
/* - SQL within the execute() block must be in Postgres syntax     */
proc sql;
  connect using pg_perm;
  execute by pg_perm
    (
      update prepsegdr.class m
        set age=t.age, weight=t.weight
        from class_updt t
        where m.name=t.name;
    );
  disconnect from pg_perm;
quit;

/* print pg master table after update */
proc sql;
  select *
  from pg_perm.class
  order by name;
quit;

The "common" libname connection options might need to be different in your environment (like for dbclient_max_bytes). You find that documented here. 

Easiest is normally to use an already existing and working libname as your starting point. 

The bits that are not in the common section are MUST and will be the same for you.

I'm showing an example for an UPDATE but of course once you've loaded all your data into Postgres tables it's just Postgres SQL you need for any other operation.