topic Re: ERROR When Updating POSTGRES TABLE using SAS DATASET in SAS Programming

ERROR When Updating POSTGRES TABLE using SAS DATASET

Santt0sh — Wed, 08 Sep 2021 23:49:12 GMT

Hi All,

I had asked a similar question, where the table was getting into the update query which was solved by adding DBDIRECTEXEC to the Autoexec files. But this time while updating I'm facing another issue.

When I am trying to update the Postgres table with a SAS dataset (WORK.W25P3TOZ)

I'm getting the below error:

ERROR: CLI execute error: ERROR: relation "work.w25p3toz" does not exist;

Error while executing the query.

I have made sure the Data length and type match with the Postgres table.

I remember I have updated the oracle tables using the Pass-thru facility and libname.

Here the Libname facility doesn't work and keeps running for ages and after completion of the update query or canceling the query

No message is displayed nor any Error is displayed.

but when I try to create the Work, w25p3toz dataset on the schema the update works.

I also tried updating the Postgres table using the SAS data step update, but it didn't work.

Can any of the Experts suggest to me how do I update the Postgres table from a SAS Dataset?

proc sql;
connect to postgres as PSTGRE1
(server="xxxxxxxxxxx.abcdefghik.in-south-1.rds.amazonaws.com" 
database="yyyyyyyy" user="lllllll" password="XyuIkIOILJM<" 
connection=global);
execute
(update CORE.FSC_PARTY_DIM as m set 
party_date_of_birth=(select party_date_of_birth from WORK.W25P3TOZ as t 
where m.party_key=t.party_key), residence_country_code=(select 
residence_country_code from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), citizenship_country_code=(select 
citizenship_country_code from WORK.W25P3TOZ as t 
where m.party_key=t.party_key), org_country_of_business_code=(select 
org_country_of_business_code from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), email_address=(select email_address from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
phone_number_1=(select phone_number_1 from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), phone_number_2=(select phone_number_2 from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
phone_number_3=(select phone_number_3 from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), occupation_desc=(select occupation_desc from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_type_desc=(select party_type_desc from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), party_tax_id=(select party_tax_id from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_identification_id=(select party_identification_id from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_identification_type_desc=(select party_identification_type_desc 
from WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_id_state_code=(select party_id_state_code from WORK.W25P3TOZ as t 
where m.party_key=t.party_key), customer_since_date=(select 
customer_since_date from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), doing_business_as_name=(select 
doing_business_as_name from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), ultimate_parent_name=(select 
ultimate_parent_name from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), party_name=(select party_name from 
WORK.W25P3TOZ as t where m.party_key=t.party_key)
WHERE exists (select * from WORK.W25P3TOZ t WHERE 
m.party_key=t.party_key)) by PSTGRE1;
disconnect from PSTGRE1;
Quit;

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

ChrisNZ — Thu, 09 Sep 2021 01:57:17 GMT

All the code in green is run by POSTGRES, the code in blue is run by SAS.

proc sql;
connect to postgres as PSTGRE1
(server="xxxxxxxxxxx.abcdefghik.in-south-1.rds.amazonaws.com" 
database="yyyyyyyy" user="lllllll" password="XyuIkIOILJM<" 
connection=global);
execute
(update CORE.FSC_PARTY_DIM as m set 
party_date_of_birth=(select party_date_of_birth from WORK.W25P3TOZ as t 
where m.party_key=t.party_key), residence_country_code=(select 
residence_country_code from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), citizenship_country_code=(select 
citizenship_country_code from WORK.W25P3TOZ as t 
where m.party_key=t.party_key), org_country_of_business_code=(select 
org_country_of_business_code from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), email_address=(select email_address from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
phone_number_1=(select phone_number_1 from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), phone_number_2=(select phone_number_2 from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
phone_number_3=(select phone_number_3 from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), occupation_desc=(select occupation_desc from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_type_desc=(select party_type_desc from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), party_tax_id=(select party_tax_id from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_identification_id=(select party_identification_id from 
WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_identification_type_desc=(select party_identification_type_desc 
from WORK.W25P3TOZ as t where m.party_key=t.party_key), 
party_id_state_code=(select party_id_state_code from WORK.W25P3TOZ as t 
where m.party_key=t.party_key), customer_since_date=(select 
customer_since_date from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), doing_business_as_name=(select 
doing_business_as_name from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), ultimate_parent_name=(select 
ultimate_parent_name from WORK.W25P3TOZ as t where 
m.party_key=t.party_key), party_name=(select party_name from 
WORK.W25P3TOZ as t where m.party_key=t.party_key)
WHERE exists (select * from WORK.W25P3TOZ t WHERE 
m.party_key=t.party_key)) by PSTGRE1;
disconnect from PSTGRE1;
Quit;

The message is quite clear:

ERROR: relation "work.w25p3toz" does not exist;

Either upload the SAS table or rework your query.

Also your code is awfully (un)formatted and impossible to read.

Also I hope that's not your real password.

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

Santt0sh — Thu, 09 Sep 2021 03:38:08 GMT

Hi,

Thank you for your reply!!!
Yes, the connection details provided here are mocked.
I can see the SAS dataset which is created by the SAS program. I m not sure how can this be not accessible to the Pass thru. I can’t load the Work table because of the data protection policy.

I am trying to run the SAS DI studio code on SAS Studio.

I m not sure if anyone else has faced the same issue.

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

SASKiwi — Thu, 09 Sep 2021 04:25:57 GMT

The table work.w25p3toz has to be a Postgres table as you are using SQL Passthru. Anything in the Passthru statement runs only in Postgres so will have no knowledge of SAS tables.

To load a SAS table in Postgres you need to run either use SAS SQL or SAS code.

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

Kurt_Bremser — Thu, 09 Sep 2021 06:55:18 GMT

If you need to use a SAS table as an update for a remote DBMS table, you either have to upload the SAS table and do the update in the pass-through, or try to do the update without explicit pass-through and hope that SAS can push most of the processing to the DBMS.

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

ChrisNZ — Thu, 09 Sep 2021 08:26:02 GMT

The issue is really simple: the data must be available where the code runs.

All the code that runs in POSTGRES must be able to see the data it processes. Likewise for the code that runs in SAS.

POSTGRES does not see SAS's WORK library.

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

Patrick — Thu, 09 Sep 2021 11:30:33 GMT

To combine data this data must be available on the server where the "join" needs to happen. In your case that's Postgres and though you need to first load your SAS table to Postgres.

"I can’t load the Work table because of the data protection policy."

It's possible that you can't create a new table in the core schema. It's hopefully still possible to load into a temporary Postgres table - which SAS supports. If that's not possible then you need to talk to your DBA.

The link here should get you started. Follow the steps for Pushing Updates.

Once loading into a temporary table works I'd also strongly recommend that you revisit your SQL. If you Google for SQL to update a table with another table you will find examples with much simpler code that will also perform much better. A sub-select for every single column is just awfully inefficient.

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

Santt0sh — Fri, 10 Sep 2021 21:24:29 GMT

Hi Patrick,

Thank you for your time and valuable suggestions.

Now I have created a table on Postgres, and I am able to update the Pty_dim Postgres table.

Best,

Santtosh...

Re: ERROR When Updating POSTGRES TABLE using SAS DATASET

Patrick — Sat, 11 Sep 2021 02:29:43 GMT

@Santt0sh That's good to hear. Please mark the answer that's the solution/helped you most as solution.

Just as a side remark: Isn't the pty table a SCD2 table? And if so wouldn't you need to INSERT a new row if any of the columns you change are under SCD2 change tracking - and expire the current record by updating columns valid_to_dttm and change_current_ind?