topic Re: Update multiple table in SAS Programming

Update multiple table

nid197 — Thu, 18 Jul 2019 13:35:16 GMT

Hi,
I have the below proc sql query where c_accnt table
has columns present in attached excel(under updt_col_nm variable).where there are 5columns for c_accnt table.and 1 column each for remaining table.i have to do the same process of updating table in a efficient way. Which should be a dynamic way to do it.can we do this in single query?I have 20 table and their corresponding columns.
Pls note:
"Updt_col_nm"(varaible name in attached excel) is derived from "thr_pt_acct_nr_col_nm".

I have used below query to update single table-

Proc sql,
Connect to teradata as tera(server="&eiwdb" authdomain="&segkc" mode=teradata);
Execute(
Update hst
From libname.c_accnt hst,
Libname.tb_rflkup lkp
Set
C_account_no=substr(lkp.new_thr_pt_acct_nr,7,23),
C_account_no_cl=substr(lkp.new_thr_pt_acct_nr,7,23),
C_company_id=substr(lkp.new_thr_pt_acct_nr,1,3),
C_prd_cd=substr(lkp.new_thr_pt_acct_nr,4,3),
Account_number=lkp.new_thr_pt_acct_nr
Where hst.account_number=lkp.old_thr_pt_acct_nr;
)by tera;
Disconnect from tera;
Quit;

Re: Update multiple table

Tom — Thu, 18 Jul 2019 13:43:59 GMT

Your query is being pushed into the remote database. So the data references need to point to data in that database.

Teradata is not going to recognize any SAS librefs or SAS datasets.

Re: Update multiple table

nid197 — Thu, 18 Jul 2019 13:47:25 GMT

Here ips the excel sheet i have mentioned in above que.i had to type the table contents here.first row is the variable name.

tbl_nm thr_pt_acct_nr_col_nm updt_col_nm
wire_sm account_number account_number
pfa_histry ent_nbr ent_nbr
c_accnt account_number c_account_no
c_accnt account_number c_account_no_cl
c_accnt account_number c_company_id
c_accnt account_number c_prd_code
c_accnt account_number account_number
pfa_agg account_nbr ent_nbr
pfa_agg account_nbr account_nbr

It would be great if anyone could help me out.really couldnt find a solution to this
Thanks:)

Re: Update multiple table

nid197 — Thu, 18 Jul 2019 13:55:51 GMT

Hi Tom,
Yes the above query gave me the results but i am just looking an efficient and less time consuming way to do it..i have to write the same query for all the other tables.this makes the program quite lengthy.can i do it using macros or so any loops.i believe we cant update multiple teradata tables at the same time.and even if we can the variables to be updated in those tables are not same they are different.some tables have 5 variable to be updated some just have 1.
I hope you get my question now.

Re: Update multiple table

Reeza — Thu, 18 Jul 2019 14:59:31 GMT

Tutorial on turning a program into a macro

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@nid197 wrote:
Hi Tom,
Yes the above query gave me the results but i am just looking an efficient and less time consuming way to do it..i have to write the same query for all the other tables.this makes the program quite lengthy.can i do it using macros or so any loops.i believe we cant update multiple teradata tables at the same time.and even if we can the variables to be updated in those tables are not same they are different.some tables have 5 variable to be updated some just have 1.
I hope you get my question now.