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...
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;
I need all the records so i can sum the amt later
Changed it to this:
Thanks Tom,
Same error. I did notice the missing parenthesis and corrected it.
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.
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;
thank you!
@TheNovice: From what I see here, you have an unbalanced single quote.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.