topic Re: Joining 3 tables via sql passthrough in SAS Programming

Joining 3 tables via sql passthrough

TheNovice — Thu, 11 Jun 2020 16:19:51 GMT

Hi all,

I am fairly comfortable joining 2 tables in passthrough but have never done 3...

Tables are like this:

Table A
ID	Date
123	28-May-20
456	29-May-20

Table B
prp_id	prp_date	PrP_SEQ_NO
123	28_may-20	80143418

Table c
ID	PrPI_SEQ_NO	PrPI_AMOUNT
123	80143418	100
123	80143418	50

Output would be:

ID	Date	PRP	sum_prp_amt
123	28-May-20	Y	150

So A.id = b.id to get PrP_SEQ_NO

and then b.PrP_SEQ_NO = c.PrP_SEQ_NO to get amount which needs to be group by PrP_SEQ_NO

can all this be done in pass through? Table b and c reside on oracle database and table a is the base table that i need to join to.

i have joined A and B successfully but getting stumped on adding in C. Any direction is appreciated

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

CASE WHEN (B.prp_date) ^=. THEN 'Y' ELSE 'N' END AS PrP

from Abc a left join connection to ORADB

  (select prp_id,prp_date
     from ods.promise
     where prp_date >= '01APR2020' and prp_status <> 'C'

) b

  on a.id= B.prp_id
  and a.Date = datepart(b.prp_Date)
  group by a.id

;
Disconnect from ORADB;

quit;

Re: Joining 3 tables via sql passthrough

smantha — Thu, 11 Jun 2020 16:47:22 GMT

proc sql;
CONNECT TO ORACLE AS ORADB (user=&NAME password=&pASS  path=ExaODIN preserve_comments);
create table Prp as
select  a.*,
CASE WHEN (B.prp_date) ^=. THEN 'Y' ELSE 'N' END AS PrP
from Abc a left join connection to ORADB
   
  (select prp_id,prp_date,prpi_amount
     from (select * from ods.promise 
          where prp_date >= '01APR2020' and prp_status <> 'C' )a
      inner join
	  (select id, prpi_seq_no,sum(prPI_Amount) as prpi_amount from ods.table_C) c
	 on 
     a.prp_id=c.id
     and
	 a.PrP_SEQ_NO = c.PrP_SEQ_NO
       
          ) b

  on  a.id= B.prp_id
  and a.Date = datepart(b.prp_Date)
  group by a.id

;  
Disconnect from ORADB;
quit;

Re: Joining 3 tables via sql passthrough

mklangley — Thu, 11 Jun 2020 17:00:30 GMT

Adding onto @smantha's solution, just include

,b.prpi_amount as sum_prp_amt

in the first select statement.

Re: Joining 3 tables via sql passthrough

TheNovice — Fri, 19 Jun 2020 22:23:34 GMT

It worked. Thank you so much!!

Re: Joining 3 tables via sql passthrough

TheNovice — Fri, 19 Jun 2020 22:24:06 GMT

I did, thank you very much