topic Re: SQL Pass Through Query Records in New SAS User

SQL Pass Through Query Records

ssaha — Wed, 01 May 2019 15:42:21 GMT

Inserting records into Oracle database using SQL passthrough query doesn’t shows the number of records inserted to the table.

Am using SQL passthrough query for inserting records to the DB table in Prod SQL.

The macro code is being included in the parent code that is scheduled for processing. When I run manually the macro code in SAS EG, I can see the number of records inserted in the table.

But when running in the include code in the parent code together, it doesn’t shows any logs messages on how many records are inserted in the oracle table.

In SAS EG – can see –

ORACLE: 0 rows inserted/updated/deleted. 153 1556630270 no_name 0 SQL (2)

While running in the parent code – cannot see the records inserted in the oracle table as in below log. Already using %PUT &SQLXRC. &SQLXMSG. ; and this only shows 0 as return code is zero with no error message –

MPRINT(EXECUTESEQUERY): *SE scrub stg;

MPRINT(EXECUTESEQUERY): proc sql;

MPRINT(EXECUTESEQUERY): CONNECT TO oracle ( path=xxx authdomain="xx") ;

MPRINT(EXECUTESEQUERY): execute ( INSERT INTO tablename ( col1, col2 ) select

app_transaction_key, T. scrub_key, sum(T.flag) as override_type from ( select a.app_transaction_key, b.scrub_key, 1 as flag from

schema.v_transaction_current a, schema.master b where trunc(a.s_effective_from) > '30-APR-2019' and

b.query_group_key = 80 and WAREHOUSE_CODE='PPP' AND CUSTOMER_NAME='SAN' union Select a.app_transaction_key, a.scrub_key, -1 as

flag from schema.v_transaction_current c, schema.v_tran_current a, schema.master b where

c.app_transaction_key=a.app_transaction_key and b.scrub_key = a.scrub_key and trunc(c.s_effective_from) > '30-APR-2019' and

b.query_group_key = 80 ) t group by T.app_transaction_key, T.scrub_key ) by oracle ;

MPRINT(EXECUTESEQUERY): Disconnect from oracle;

MPRINT(EXECUTESEQUERY): quit;

NOTE: PROCEDURE SQL used (Total process time):

Re: SQL Pass Through Query Records

Tom — Wed, 01 May 2019 15:46:18 GMT

Are you asking what ORACLE code you can use to mimic the SAS feature that displays the number of records inserted?

You might just try counting number of observations before and after the insert.

Re: SQL Pass Through Query Records

ssaha — Wed, 01 May 2019 18:43:54 GMT

Thanks for your reply

Yes, want to know what macro variables or code can help me know how many rows were inserted in the table using SAS SQL pass through query.

as we can do that count of rows after and before, but that is again going to add processing time.
Is there a way we can know from logs that how many rows were inserted like we get when we use libname statement or when we run it as a simple code and not include as sas code in another SAS Code.

Re: SQL Pass Through Query Records

rajdeep — Tue, 16 Feb 2021 08:07:05 GMT

Hi ssaha OBSIDIAN,

You can enable the sastrace from options tab, for the specific transformation if the code is running under DI Studio or else if it's running in BASE then you can pass the below option just right above the passthrough step.

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

The log will give you some information like this:

ORACLE: 158 rows inserted/updated/deleted.

Thanks and have a great day ahead.