BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TheNovice
Quartz | Level 8

hi all,

 

I have 2 tables like below:

Table A:

   
iddateuser
12301-Jan-21abc
12301-Jan-21bdf
22201-Jan-21abc

 

Table b:

b   
id bdateuseramt
12301-Jan-21abc5
12301-Jan-21abc10
22203-Jan-21bdf20

 

Output i need is:

 

iddateusersum(amt)count (ID)
12301-Jan-21abc152
12301-Jan-21bdf02
22201-Jan-21abc0

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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;

 

View solution in original post

7 REPLIES 7
ChrisNZ
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8

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:

 

iddateusersum(amt)count (ID)
12301-Jan-21abc152
12301-Jan-21bdf02
22201-Jan-21abc01

 

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

ChrisNZ
Tourmaline | Level 20
What sort of percentage do you keep? How many different values for ID and USER in table A?
TheNovice
Quartz | Level 8

in the hundred thousands

ChrisNZ
Tourmaline | Level 20

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;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2479 views
  • 0 likes
  • 2 in conversation