proc sql WITH

[Mchan890]

I have this query below that works in Oracle, however, needed to run in SAS proc sql to join with other data. Does anyone know how to convert this statement to work with proc sql? Also, if someone is familiar with this output, how come with the invoice date range set between April and May 2021, this query still pulls in ALL dates in the ouput? 

with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
select vendor_id,invoice_id, pay_amt,count(*)
from ps_voucher
where invoice_dt between '01-Apr-2021' and '31-May-2021'
group by vendor_id,invoice_id, pay_amt
having count(*)>1)
select t.vendor_id, t.voucher_id,t.INVOICE_ID,t.gross_amt, t.INVOICE_DT
from ps_voucher t
join pay_cte p on t.vendor_id=p.vendor_id and t.invoice_id = p.invoice_id
and t.gross_amt=p.pay_amt

[ballardw]

I don't speak Oracle.

What does this do?

with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as

[Reeza]

If you explain the logic someone can help with the code conversion. SAS has a lot of functionality that doesn't require Common Table Expression functionality but its easier if you explain what it's doing.

[Mchan890]

the WITH statement in Oracle is looking for a similar match of (vendor_id, invoice_id, gross_amt) having more than 1 match for potential duplicates

The logic is trying to identify potential match in the data for a set of criteria (vendor_id, invoice_id, gross_amt) ... another would be for (invoice_id, invoice_dt, gross_amt) for example

[Reeza]

Since there's a join this isn't quite a duplicate situation in my head. Can you please illustrate the problem with some data as examples? If you cannot use your data please feel free to use data in the SASHELP library.

[SASKiwi]

Where is the "other data" coming from? If it is SAS data and your Oracle data is large, it would be more efficient to load your SAS data as an Oracle temporary table and continue to do all of your processing in Oracle.

[Sajid01]

1.SAS does not support the Oracle WITH Clause.

2.If the code you have given is exactly the code you want to run, then the join is not needed. You are pulling the data from ps_voucher in both the WITH clause subquery and join. In fact the way the join is structured it pulls all the data fro ps_voucher. In order to get data for invoice date range set between April and May 2021, the following query should suffice. Do make changes as needed. I don't have your data to test it.

proc sql;
create table pay_cte (drop=cnt) as
select vendor_id, voucher_id, INVOICE_ID, gross_amt, INVOICE_DT, count(*) as cnt
from ps_voucher
where invoice_dt between '01-Apr-2021' and '31-May-2021'
group by vendor_id,invoice_id, pay_amt
having count(*)>1 ;
quit;

[DaanDNR]

Something like this? Note that you should use the numeric value for the dates to get an accurate selection.

proc sql;
  select t.vendor_id
       , t.voucher_id
       , t.INVOICE_ID
       , t.gross_amt
       , t.INVOICE_DT
  from ps_voucher t join
    (select vendor_id,invoice_id, pay_amt, count(*) as pay_cnt 
     from ps_voucher
     where invoice_dt between '01APR2021'd and '31MAY2021'd
     group by vendor_id, invoice_id, pay_amt
     having count(*)>1) p 
  on t.vendor_id = p.vendor_id
  and t.invoice_id = p.invoice_id
  and t.gross_amt=p.pay_amt
  ;
quit;