Hoping someone can help.
I am using SAS Enterprise Guide 4.3 - connecting into Oracle to pull back data. The information I am returning is personid Status, Start Time, End Time and Last Date Timestamp.
Here is the problem - for the Last Date Timestamp, I am trying to determine a way to only return the records for the most recent timestamp. Below is the code that is running, but it has been running for over 3 hours.
select * from connection to oracle
( select
abc.person_id
,abc.status
,abc.start_time
,abc.end_time
,tmp.last_ld_ts
From
[my table name] abc
,(select max(to_number(to_char(last_ld_ts, 'YYYYMMDD'))) as max_last_ld_ts from [my table name]) tmp
where
abc.person_id = 5897 and
to_number(to_char(abc.last_ld_ts, 'YYYYMMDD')) = tmp.max_last_ld_ts
);
disconnect from oracle;
Should your sub-query have a WHERE clause for person_id? Otherwise, how do you know you're getting the latest date for that person, as opposed to just the general latest date?
However, that shouldn't make it run slow, it should just return no data. I don't see anything offhand that should cause long delays. Two general suggestions for troubleshooting:
1. Try running this query under your SQL client directly (is it still SQL*PLUS?), and see how long it takes. If it returns what you're expecting very quickly, then the problem is probably on the SAS side. Otherwise, it's probably on the Oracle side, and has nothing to do with SAS.
2. SAS has a SASTRACE option that can be used to display the actual code that SAS sends to Oracle. Although in this case I believe it will be exactly the code you've written, it could be worth a try.
Keep us posted!
Tom
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.