topic Re: Applying 2 conditions in a pass through in SAS Programming

Applying 2 conditions in a pass through

TheNovice — Mon, 30 Dec 2019 15:47:38 GMT

hi all,

can't seem to figure this out. I have a table A of ID's and ID_Date and I need to extract records from table B

by matching ID where ID_Date >= Table B_Date

date in table A is in date format and date in table b is in datetime format

proc sql;
CONNECT TO ORACLE (user=&name pw=&pass path=Exa);
create table P_1 as
  select * from connection to oracle
  (select ID,SEQ_NO,Tableb_DATE,AMT
   from p_act
       )

( WHERE ID IN (SELECT ID FROM table a)

and ID_date > tableb_Date )
;
disconnect from oracle;
quit;

I keep getting an error. not sure how to go about this...

Re: Applying 2 conditions in a pass through

tomrvincent — Mon, 30 Dec 2019 15:53:43 GMT

What's the error?
I'd add a distinct to that ID select and maybe a trunc of tableb_date.

Re: Applying 2 conditions in a pass through

TheNovice — Mon, 30 Dec 2019 16:42:13 GMT

I need all the records so i can sum the amt later

Changed it to this:

proc sql;
CONNECT TO ORACLE (user=&name pw=&pass path=Exa);
create table P_1 as
select * from connection to oracle
(select ID,SEQ_NO,Tableb_DATE,AMT
   from p_act
       )

( WHERE ID IN (SELECT ID FROM table a)
and ID_date > trunc(tableb_Date),'DD-Mon'YY' )
    ;
disconnect from oracle;
quit;

error i get now is :

                                       22
                                               200
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
              GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
              ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: Applying 2 conditions in a pass through

tomrvincent — Mon, 30 Dec 2019 16:56:22 GMT

you've got several errors there. Try this instead:

( WHERE ID IN (SELECT DISTINCT ID FROM table a)
and ID_date > trunc(tableb_Date)

Re: Applying 2 conditions in a pass through

tomrvincent — Mon, 30 Dec 2019 16:59:48 GMT

I missed a paren, but also you have nothing that joins a and p_act...both Oracle tables?

Re: Applying 2 conditions in a pass through

TheNovice — Mon, 30 Dec 2019 17:03:30 GMT

Thanks Tom,

Same error. I did notice the missing parenthesis and corrected it.

   _
                                                22
                                                200
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, EXCEPT,
              GE, GET, GROUP, GT, GTT, HAVING, INTERSECT, LE, LET, LT, LTT, NE, NET, NOT, OR, ORDER, OUTER, UNION, ^, ^=, |, ||, ~,
              ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

Table a is not an oracle table. it's a table i have created. I am not very good at nested arguments yet so i used this method instead.

Re: Applying 2 conditions in a pass through

tomrvincent — Mon, 30 Dec 2019 19:44:21 GMT

Okay...so you need to do the oracle stuff all together and then join the results to a.

proc sql;
 CONNECT TO ORACLE (user=&name  pw=&pass  path=Exa);
 create table P_1 as
  select p1.*,a.* from connection to oracle
  (select ID,SEQ_NO,Tableb_DATE,trunc(tableb_Date) as p1date,AMT
   from  p_act
       ) p1
   left join a on p1.id=a.id and a.id_date > p1date
    ;
 disconnect from oracle;
quit;

Re: Applying 2 conditions in a pass through

hashman — Tue, 31 Dec 2019 00:23:18 GMT

@TheNovice: From what I see here, you have an unbalanced single quote.

Re: Applying 2 conditions in a pass through

TheNovice — Fri, 03 Jan 2020 03:28:36 GMT

thank you!