Pulling records based on a range in Oracle pass through

TheNovice — Wed, 08 Jan 2020 21:00:15 GMT

I have 2 tables with a large number of records (100k+).

Table B is the base that i have extracted and need to join to table B.

I have to pull invoices for records for the previous 3 months. Where i am getting lost is on how to account for pulling records for the previous year if the date i have is for Jan.

I need to pull records where bill month falls in the 3 months before the sus month. Meaning 3 records.

I have tried this but get no records... and i realize that it won't help me if the Sus_month value is 1 . The months being from 1 - 12.

and I have no idea on how to account for cases where it will need to pull from 2018

PROC SQL;
CONNECT TO ORACLE (USER=&NAME PW=&PASS PATH=EXAODIN);
CREATE TABLE CHARGE AS
  SELECT B.*,A.* FROM CONNECTION TO ORACLE
  (SELECT ID,SUB,BILL_YEAR,BILL_MONTH
   FROM OS.CHARGE
) A INNER JOIN TEST2 B
    ON B.ID= A.ID
    WHERE A.BILL_MONTH BETWEEN (B.SUS_MONTH -3) AND (B.SUS_MONTH -1)

;
DISCONNECT FROM ORACLE;
QUIT;

Re: Pulling records based on a range in Oracle pass through

Tom — Wed, 08 Jan 2020 21:08:02 GMT

It looks like you are tying to join the Oracle table OS.CHARGE to the SAS dataset WORK.TEST2.

If OS.CHARGE is large then you will probably need to upload a copy of TEST2 into Oracle in order to prevent SAS from pulling ALL of OS.CHARGE over to SAS.

If you are getting syntax errors then please show the lines from the SAS log. Make sure to paste in the lines of text from the log (not a Polaroid of your screen) using the Insert Code button on the editor so that the formatting is preserved.

topic Pulling records based on a range in Oracle pass through in SAS Programming

Pulling records based on a range in Oracle pass through

Re: Pulling records based on a range in Oracle pass through