topic UPDATE a CORE TABLE Postgres via SAS Studio. in SAS Programming

UPDATE a CORE TABLE Postgres via SAS Studio.

Santt0sh — Mon, 06 Sep 2021 03:23:43 GMT

Hi Experts,

I am not able to update one of the Core Dim tables on Postgres from a SAS Code.

I am using SAS AML 8.2 on SAS Viya3.5. have modified the existing SAS DI Jobs and trying to run the SAS programs on SAS Studio I'm not sure why I am not able to update the table Postgres Core table, but I am able to insert rows into the same table.

Kindly note that I have not tried updating any other Core Tables as I need to make changes to SAS programs to insert or update records to other Dim tables.

I have tried adding messages before the update query, the messages are printed but the update query is not initiating, or even if it's initiated I don't see any Error messages, Warning with respect to the update and SAS stops processing. I have also reset the &syscc.,&sqlrc.,&syserr., &trans_rc, &trans_rc to 0. which should not stop the code from processing. Please find the update query below that has changed the names of the table and variables.

---------------------------------------------------------------------------------

/* %rcSet(&syscc); */
%let syscc = 0;
options validvarname=v7;
%put &syscc.;

/* target table: update change rows */
%if &etls_change_rows ge 1 %then
%do;
proc sql;
update CORE.CRE_PARTY_DIM as n
set
prty_date_of_birth = (select prty_date_of_birth from work.xxxxxxxxx as k
where n.prty_key = k.prty_key),

other Update columns conditions continue....

-------------------------------------------------------------------------------------------------------

At the moment we are having some SAS Server maintenance activities going on, hence not able to provide log info for now.

Please suggest if, I am missing any privileges on the table or anything else.

Re: UPDATE a CORE TABLE Postgres via SAS Studio.

LinusH — Mon, 06 Sep 2021 08:27:52 GMT

Not sure what a core table is, and if that itself has anything to do with your problem?

You should ba ble to look into your target DB logs to see what's going on.

But lack of auhorization seems to be plausible problem.

To get information from an implict SQL statement, add this to your code:

options msglevel=i sastrace = ',,,d' sastraceloc = saslog nostsuffix;

Re: UPDATE a CORE TABLE Postgres via SAS Studio.

Santt0sh — Mon, 06 Sep 2021 11:53:11 GMT

Thanks for the reply!!!!

Core tables are nothing but Database tables which will be used to create
reports.

I have tried all the options, I have also added the log options mentioned
below but as the services were under maintenance I was not able share the
logs.

I don’t think Permission is an issue because I am able to insert records
into the cre.prty_dim table but not able to update the records.

I hope the services will be up soon I’ll try to run the code and share the
logs here.

Re: UPDATE a CORE TABLE Postgres via SAS Studio.

LinusH — Mon, 06 Sep 2021 13:53:25 GMT

INSERT and UPDATE are two different permissions in Postresql, so I wouldn't rule that out...

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

Re: UPDATE a CORE TABLE Postgres via SAS Studio.

Santt0sh — Tue, 07 Sep 2021 18:01:16 GMT

Hi,
Thanks for your help,
DBIDIRECTEXEC was missing from Autoexec's...
After the Admin added it, it started working....

Appreciate all the Help and Suggestions!!!!