Summing within nested sql query in pass through

[TheNovice]

hi all,

I have 2 tables like below:

Table A:

id	date	user
123	01-Jan-21	abc
123	01-Jan-21	bdf
222	01-Jan-21	abc

Table b:

b
id	bdate	user	amt
123	01-Jan-21	abc	5
123	01-Jan-21	abc	10
222	03-Jan-21	bdf	20

Output i need is:

id	date	user	sum(amt)	count (ID)
123	01-Jan-21	abc	15	2
123	01-Jan-21	bdf	0	2
222	01-Jan-21	abc	0	1

Now, i know how to get the output until sum(amt)

proc sql;
CONNECT TO ORACLE AS ORADB (user=&NAME password=&pASS path=ExaODIN preserve_comments);
create table same as
select
d.*,sum(b.amt) as amt
from A D
left join connection to ORADB
(select id, date,user,amt
from Table B



) e
on A.id = B.id
and (A.date) = (b.date)
AND A.user = b.user
group by a.id,b.user_id

;
Disconnect from ORADB;

quit;

But I i don't know how to get the count of the ID as well... I can't seem to get the syntax right for the nested sub query that will sum the amt by user first and then let me count the total ids 

Hope this makes sense

[ChrisNZ]

Please post your code using the appropriate icon.

Is table A a SAS table and table B an Oracle table?

The syntax could be something like:

libname ORADB oracle user=&NAME password=&pASS path=ExaODIN preserve_comments;
proc sql;
  connect using ORADB;
  create table SAME as
  select A.*, B.USER_ID, sum(B.AMT) as AMT
  from A 
         left join B
       ORADB.B
         on  A.ID   = B.ID
         and A.DATE = B.DATE
         and A.USER = B.USER
  group by A.ID, B.USER_ID
;
quit;

Also do not use uppercase randomly in your code. Or parentheses. Use these to make your code more legible, rather than messier.

[TheNovice]

Hi Chris,

Apologies, I was trying to conceal variable names and table names and did a shoddy job.

Table A is a local sas table = Let's call it Accounts

Table B is in oracle = Let's call it Amounts. This is a huge table. Hence the use of passthrough

My code is similar to your code. 

However,  The sum(amt) has to be grouped by user and the count(id) has to be grouped by Id only. This is where i am stuck. I could just write a second query to get the count of ID but i would like to do it in the nested query if possible. 

proc sql;
CONNECT TO ORACLE AS ORADB (user=&NAME password=&pASS path=ExaODIN preserve_comments);
create table BIF as
select
a.*,sum(b.amt) as Amt

from Accounts a
left join connection to ORADB
(select BAN, PYM_METHOD,deposit_Date,user_id, original_amt
from ODS.Amounts

) b
on A.id = B.id
and a.date = b.date
AND a.user = b.user
group by a.id

[ChrisNZ]

Are USER and ID linked somehow? As in all USERs in one ID cannot be found in another ID? 

What is the value of the SAS table here since you do an qui join in ID and USER? You could do the summary wholly in Oracle. Is it to subset the Oracle table?

[TheNovice]

ID represents accounts and the users are the people workings those accounts. Multiple users can manage amounts on one accounts and these amounts are separate transactions. SO i need to sum of all the amounts by user and then i need to count the number of times the account appears. Like below:

id	date	user	sum(amt)	count (ID)
123	01-Jan-21	abc	15	2
123	01-Jan-21	bdf	0	2
222	01-Jan-21	abc	0	1

Yes, it is to subset the oracle table. the sas table has other information that is not found in that oracle table

[ChrisNZ]

What sort of percentage do you keep? How many different values for ID and USER in table A?

[TheNovice]

in the hundred thousands

[ChrisNZ · Accepted solution]

It seems you should reduce the volume as much as possible in Oracle:

Sum the amounts and count the records, using group by ID, USER. This is the pass-through query.

This query is joined to the SAS table.

The syntax could be something like:

libname ORADB oracle user=&NAME password=&pASS path=ExaODIN preserve_comments;
proc sql;
  connect using ORADB;
  create table SAME as
  select A.*, SUM, COUNT
  from A 
    left join connection to ORADB 
      (select ID, DATE, USER, sum(AMT) as SUM, count(*) as COUNT 
       from B
       group by A.ID, B.USER_ID)
    on  A.ID   = B.ID
    and A.DATE = B.DATE
    and A.USER = B.USER
;
quit;