topic Re: Best way to do SQL Update from another table in SAS Programming

Best way to do SQL Update from another table

WaiLoon — Thu, 29 Sep 2022 02:25:16 GMT

I need to update my current working table from source database table.

Update HardwareList tgt

SET Remark = (SELECT Remark FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')

,Status = (SELECT Status FROM TmpHW WHERE tgt.ID= TmpHW.ID and InsertUpdate = 'U')

,DecomDate = (SELECT DecomDate FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')

,DecomReason = (SELECT DecomReason FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U')

I need to have 1 select statement for each field that I need to update, if i got 30 fields need to update means I need 30 select statement and I worry this will cause the performance issue cause too many extraction.

Is there any best method to do update?

Other database system like MSSQL, MySQL, PostgreSQL, the update statement can be very simple

example

Update HardwareList tgt

SET Remark = Remark

,Status = Status

,DecomDate = DecomDate

,DecomReason = DecomReason

FROM TmpHW WHERE tgt.ID = TmpHW.ID and InsertUpdate = 'U'

I really having issue with the performance for the update, and I really hope to see if there are any way to improve it.

Re: Best way to do SQL Update from another table

SASKiwi — Thu, 29 Sep 2022 03:03:34 GMT

Are you updating an external database ? If so is that data coming from SAS or from the external database? If you are updating an external database from SAS then one easy way is to load a temporary table into the database from SAS and then run an SQL database query updating from the temporary table. SQL Passthru allows you to run database-specific SQL from SAS.

Re: Best way to do SQL Update from another table

WaiLoon — Thu, 29 Sep 2022 03:17:49 GMT

Thanks for fast reply.

Yes, I'm updating to PostgreSQL from SAS.

Temp table means physical table or just table in memory??

Do you have sample or details??

How I do now is, I create a tmp table with all data I need from SAS table

Then I have 1 extra column call InsertUpdate, this column will differentiate whether the record is new or existing.

then Insert into PostgreSQL for InsertUpdate = 'I' and update data if InsertUpdate = 'U'.

I have create index key in postgreSQL for the updating but still very slow.

Re: Best way to do SQL Update from another table

SASKiwi — Thu, 29 Sep 2022 03:49:09 GMT

If you are either updating or inserting all columns in a row, then one easy way is to delete the rows you are updating and just do a single insert. Does that work for you? It is easy to insert from a SAS table using PROC DATASETS with the APPEND statement.

Re: Best way to do SQL Update from another table

WaiLoon — Thu, 29 Sep 2022 08:28:50 GMT

Thanks for the reply.

Actually I can't do that, reason is the target table contain data from source and also user input value.

Some fields are updating from SAS, but some fields are user input from the web ui. so I can't delete the record.

Btw, how to do the Proc Dataset with Append?

I'm new to SAS, I'm only good at SQL, so now sure how to do that...

Re: Best way to do SQL Update from another table

SASKiwi — Thu, 29 Sep 2022 19:09:25 GMT

This is how I would load into an SQL Server temporary table:

libname tempdb odbc noprompt = "server=MySQLServer;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;DATABASE=tempdb;" Schema = DBO;

proc datasets library = tempdb;
  append base = MySQLTempTable
         data = work.MySASTempTable 
         ;
run;
quit;

It will be a bit different for Postgres.

Re: Best way to do SQL Update from another table

WaiLoon — Fri, 30 Sep 2022 00:46:47 GMT

Hi @SASKiwi

Just to make sure i understand correctly

1. Create a temp table (tmpHW) in postgresql

2. Insert source table to the tmpHW

3. Update the tmpHW data to Hardware table using SQL

Is this the steps?

and how about the append you mentioned?

Sorry, i really need help on this cause really dont know SAS code.

The code you show me look very simple and it create the temp table within few seconds.

I would like to understand, how do i insert new record from the tmpHW to Hardware and also Update the data from tmpHW to Hardware using SAS code??

Can show some sample??

It is possible i prepare the temp data table in proc sql... then from proc datasets append the data table to postgresql?

Re: Best way to do SQL Update from another table

SASKiwi — Fri, 30 Sep 2022 01:19:54 GMT

Since you appear to be knowledgeable in database SQL you can use genuine PostgreSQL SQL syntax like this:

libname PostgreS odbc noprompt = "server=MySQLServer;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes;DATABASE=tempdb;" Schema = DBO;

proc sql;
   connect using PostgreS; 
   execute (update MyDatabase.MySchema.MyTable
   < PostgreSQL Update SQL statements>
           ) by PostgreS;
   disconnect from PostgreS;
quit;

Having the SAS table loaded as a database temporary table means you can refer to that in the above example to do updates.

Re: Best way to do SQL Update from another table

Tom — Fri, 30 Sep 2022 01:29:56 GMT

@SASKiwi wrote:

.. using PROC DATASETS with the APPEND statement.

Also known as PROC APPEND.

Re: Best way to do SQL Update from another table

WaiLoon — Fri, 30 Sep 2022 06:29:29 GMT

Then may I know how to delete the temp table??

I plan to do

1. Insert whatever new record from SAS to postgresql using normal "proc sql"

2. use "proc append" to copy the table from SAS to postgresql as a temp table

3. then use "execute" to run normal SQL statement to do the data update

4. delete the temp table from postgresql

but i'm not sure how to do the delete like the "proc append".

And also wish to know if i use "proc append" to copy a temp table to postgresql instead of physical table?

example

I create a tmptable and join few column from few different table, then copy this tmptable to postgresql.

Re: Best way to do SQL Update from another table

Kurt_Bremser — Fri, 30 Sep 2022 07:46:01 GMT

DROP TABLE should be available in Postgres SQL, so you use it in the pass-through.

Re: Best way to do SQL Update from another table

WaiLoon — Fri, 30 Sep 2022 12:45:05 GMT

I got 1 more question.

I create a tmptable and join few column from few different table, then copy this tmptable to postgresql.

CREATE TABLE Temp1 AS 
SELECT HW.Id, HW.Name, HW.Status, Type.TypeName
FROM   source.HW
INNER JOIN source.Type
ON Type.TypeID = HW.TypeID

;

proc datasets library = source;
  	append base = SASHardwareTable
           data = Temp1
    ;
run;
quit;

Re: Best way to do SQL Update from another table

Kurt_Bremser — Fri, 30 Sep 2022 20:28:09 GMT

And what is your question?

Re: Best way to do SQL Update from another table

WaiLoon — Sat, 01 Oct 2022 02:03:50 GMT

Sorry, I didnt make it clear.

I wish to proc append the join temp table to postgresql as a temp table and not physical table in SAS.

Re: Best way to do SQL Update from another table

Kurt_Bremser — Sat, 01 Oct 2022 03:25:59 GMT

You first create the temp table in the database (not in WORK), then you use INSERT INTO in explicit pass-through to append the data to the "master". In explicit pass-through (EXECUTE BY), you use the syntax of the database's SQL.

Re: Best way to do SQL Update from another table

Patrick — Sun, 02 Oct 2022 01:31:16 GMT

@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.