Hi All,   I am trying to update a table where the Country code 3 column is missing or null using the below macro. Kindly find the partial log after the macro;   I believe there is some issue with the way have written this macro because every time I run this macro I need to run it a couple of times to update all the missing records. Kindly suggest.   options Symbolgen Mlogic Mprint; %macro ints; Proc sql ; select Count(*) into :dnts from cre.FSC_COUNTRY_DIM Where UPPER(country_code_3)=' '; quit; %put &dnts.; %if &dnts. eq 0 %then %do; %put Note:Country DIM NOT EMPTY; %end; %else %do; %put &dnts.; %do i=1 %to &dnts.; Proc sql noprint; select PARTY_NUMBER, STREET_COUNTRY_CODE into :pty_num1 -: pty_num&i., :cnty_cde1 -:cnty_cde&i. from cre.FSC_COUNTRY_DIM where UPPER(country_code_3)=' '; quit; %put PARTY_NUMBER&i.==>> &&cnty_cde&i.; proc sql noprint; update cre.FSC_COUNTRY_DIM set country_code_3="&&cnty_cde&i." where PARTY_NUMBER="&&pty_num&i." and UPPER(country_code_3)=' '; quit; %put country_code_3&i.; %end; %end; %mend; %ints; ========================================================= LOGS:   SYMBOLGEN: Macro variable I resolves to 7 SYMBOLGEN: Macro variable PTY_NUM7 resolves to 0000000000xxxxxXXXXXYY MPRINT(INTS): update cre.FSC_COUNTRY_DIM set country_code_3="XXX" where PARTY_NUMBER="0000000000xxxxxXXXXXYY" and UPPER(country_code_3)=' '; NOTE: 1 row was updated in CRE.FSC_COUNTRY_DIM. MPRINT(INTS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.01 seconds MLOGIC(INTS): %PUT country_code_3&i. SYMBOLGEN: Macro variable I resolves to 7 country_code_37 MLOGIC(INTS): %DO loop index variable I is now 8; loop will iterate again. MPRINT(INTS): Proc sql noprint; SYMBOLGEN: Macro variable I resolves to 8 SYMBOLGEN: Macro variable I resolves to 8 MPRINT(INTS): select PARTY_NUMBER, STREET_COUNTRY_CODE into :pty_num1 -: pty_num8, :cnty_cde1 -:cnty_cde8 from cre.FSC_COUNTRY_DIM where UPPER(country_code_3)=' '; MPRINT(INTS): ; MPRINT(INTS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MLOGIC(INTS): %PUT PARTY_NUMBER&i.==>> &&cnty_cde&i. SYMBOLGEN: Macro variable I resolves to 8 SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable I resolves to 8 WARNING: Apparent symbolic reference CNTY_CDE8 not resolved.       ... View more

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;   ... View more

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.     ... View more