I am trying to translate a Proc SQL query to use pass-through, but to use pass-through, I have to use native SQL (the new query hits a google bigquery database). There are some functions which are failing. I know that Proc SQL sends things differently, or converts some code to "real" SQL. Is there a way that I can see that? Some option to print it to log?
Thanks!
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
Run your query and check your log with the sastrace, sastraceloc and nostsuffix options.
@ProcWes wrote:
I am trying to translate a Proc SQL query to use pass-through, but to use pass-through, I have to use native SQL (google bigquery database). There are some functions which are failing. I know that Proc SQL sends things differently, or converts some code to "real" SQL. Is there a way that I can see that? Some option to print it to log?
Thanks!
SASHELP not a DB table so there won't be any extra SQL or information, your use case indicated a DB, specifically Google Big Query.
Check the documentation for examples of what you should see when hitting a DB table, which I linked for you in my previous reply.
Well thanks for trying your best. I am hopeful that someone with more experience here will be able to reply and propose an idea!
Provide
1) how you connect to the external DB
2) The current Proc SQL that your executing
3) describe which features of the external DB you want to use that SAS Proc SQL does not provide.
SAS Proc SQL basically uses ANSI standard SQL. So general Proc SQL passes standard sql that (almost) every SQL based system uses should honor except for some SAS specific functions like date manipulation. So knowing which functions "fail" is important.
Do you have SAS/Access to Interface to Google Bigquery licensed? The main thing that the ACCESS tools provide are things like data conversion (since SAS only supports two data types in data set variables) to most of the DB data types and the connection options. I doubt there is much if any generated SQL code involved.
If you do not know the syntax for the query in the external database say so. Maybe some one can provide approaches.
How about posting the complete SAS log of one of your SAS queries that is failing? We can't give specific advice without specific evidence.
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.