Good afternoon, I need advice, please see two attach log files; first one o not know how to resolve on this. second one, only 10 records took over an hour to run is the problem, how to resolved.
Thank you
For log1 - there is a disk space issue writing to a temporary sybase table. Contact your sybase DBA.
As for log2 - your query is the select DISTINCT with OUTOBS=10.
To get that amount of output observation you probably need read much more observations,
depending on data, amount of duplicates and of variables, beyond the overhead of the connection to sybase.
log 1, I am going to contact SAS admin,
log 2, might be the disk space issue, only 10 records but took over an hour to complete.
Thank you
Joanne
Not sure why you use DISTINCT in all of your queries. These will definitely slow them down and cause Sybase to do extra sorting to confirm the uniqueness of your result set and use more database temporary space. I try to avoid using that in any SQL I write. There are other ways to ensure uniqueness by refining your queries or doing summarising. I suggest you explore each of the tables you are reading to see if DISTINCT is necessary or not.
outobs= is a SAS option. When using explicit passthrough, the SQL code in there is viewed as a "black box" by SAS, and it does not change anything in there, like setting output options. So the whole query will be executed, including all joins and sorts for distinct. SAS then returns only 10 observations from what it gets.
Regarding DISTINCT: use it only when it is needed. It forces a sort on ALL columns in the select, and you might imagine what that costs.
Your storage problems do not happen on the SAS side, they happen in the database, so you need to get in touch with the DataBase Administrator (DBA).
> only 10 records took over an hour to run
You ran the whole query. That took an hour. Your only keeping 10 records of that result will not change the passthrough query.
I can't tell you more since I will not open untrusted DOCX files, but you may be able to limit the number of rows inside the pass-through code, for example by adding a condition on ROWNUM or similar (ROWNUM is for Oracle).
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.