topic Re: Possible to see generated SQL code from Proc SQL? in SAS Programming

Possible to see generated SQL code from Proc SQL?

ProcWes — Fri, 26 Feb 2021 18:32:32 GMT

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!

Re: Possible to see generated SQL code from Proc SQL?

Reeza — Fri, 26 Feb 2021 17:56:23 GMT

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!

Re: Possible to see generated SQL code from Proc SQL?

ProcWes — Fri, 26 Feb 2021 18:07:09 GMT

That doesn't seem to do anything. There are no notes in the log. Could you show me what it looks like for you?

26 GOPTIONS ACCESSIBLE;
27 options sastrace=',,,d' sastraceloc=saslog nostsuffix;

28 proc sql;
29 create table test as select
30 *,
31 compress(model) as c,
32 case when not missing(cylinders) then 1 else 0 end as m
33 from sashelp.cars;
NOTE: Compressing data set WORK.TEST decreased size by 0.00 percent.
Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: Table WORK.TEST created, with 428 rows and 17 columns.

34 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

35
36 GOPTIONS NOACCESSIBLE;
37 %LET _CLIENTTASKLABEL=;
38 %LET _CLIENTPROCESSFLOWNAME=;
39 %LET _CLIENTPROJECTPATH=;
40 %LET _CLIENTPROJECTPATHHOST=;
41 %LET _CLIENTPROJECTNAME=;
42 %LET _SASPROGRAMFILE=;
43 %LET _SASPROGRAMFILEHOST=;
44
45 ;*';*";*/;quit;run;
46 ODS _ALL_ CLOSE;

Re: Possible to see generated SQL code from Proc SQL?

Reeza — Fri, 26 Feb 2021 18:20:06 GMT

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.

Re: Possible to see generated SQL code from Proc SQL?

ProcWes — Fri, 26 Feb 2021 18:21:17 GMT

My example said I am translating from an existing query to a BigQuery database which requires pass through.

Possible to see generated SQL code from Proc SQL?

ProcWes — Fri, 26 Feb 2021 18:34:40 GMT

Well thanks for trying your best. I am hopeful that someone with more experience here will be able to reply and propose an idea!

Re: Possible to see generated SQL code from Proc SQL?

Reeza — Fri, 26 Feb 2021 18:31:21 GMT

My understanding of the problem would be that you have a query, PROC SQL that you currently run via implicit pass through and you need to move to explicit pass through. To do that you would run your implicit query, either PROCs or SQL and you could then see the generated info sent to the DB. If you're not touching the DB and need to convert your queries manually, then you need to map the SAS functions to the Big Query functions manually.

Re: Possible to see generated SQL code from Proc SQL?

ballardw — Fri, 26 Feb 2021 22:56:04 GMT

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.

Re: Possible to see generated SQL code from Proc SQL?

SASKiwi — Sat, 27 Feb 2021 23:34:04 GMT

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.