SAS explicit pass-through with --comments converts to single line

Question

It appears that when performing an explicit pass-through with SAS, the text is not delivered to the server in the same manner that it is laid out within the editor or .sas file itself. SAS appears to be delivering the text all on a single line.

This presents a problem when you're passing a query with --comments.

Example:

The following code will be "flattened" within a PROC SQL explicit pass-through:

SELECT * --this is my comment
FROM myTable
WHERE
    1 = 1

and the server will receive it like this:

SELECT * --this is my comment FROM myTable WHERE 1 = 1

As you can see, everything after the comment's dashes is....commented.

Is anyone aware of a special option or configuration that can be used with PROC SQL to ensure this doesn't happen? I am aware that with SAS, each of those comments could be converted to:

/*comments like this*/

But for the sake of this question, let's pretend that's not an option and that it is necessary to maintain the --comments in the code.

EDIT: I should note that I am performing this explicit pass-through to an IBM Netezza database.

Answer 1

I don't believe this is a SAS issue at all but a ODBC driver issue.

I am able to replicate the issue against ODBC connections to a mySQL database, and also replicate the issue using the same ODBC connections from programs other than SAS.

Answer 2

There appears to be no documented option within proc SQL that does this. You could try asking SAS technical support to see if they have any suggestions.

My own suggestion would be to apply a regex substitution to the query string to remove any comments prior to submitting it via pass- through.

Answer 3

Alright, I really hate to answer my own question on SO, but this is the official answer from SAS Technical Support (support@sas.com):

Hello Joshua, I did some additional checking on this. Unfortunately SAS does not preserve the line breaks as you have noticed. Additionally the SAS/ACCESS engine does not have a function such as PRESERVE_COMMENTS seen in the Oracle engine. There is no real way to force this to be passed with the line breaks. The block comments will be the next best option. I am sorry for the inconvenience. Please let me know if there are any questions or concerns.

Thank you,

Sean

Answer 4

How the string is highlighted when pasted into StackOverFlow shouldn't matter. It is what the database does when it receives that string. Does it consider the double hyphen as an attempt to subtract some value that has had the negation operator prefixed to it? Or does it treat it like an end of line comment?

At least with Teradata the database is recognizing the -- as starting a comment, but since it is only getting one line of code it comments out all of the rest of the code.

 70         select * from connection to td
 71         (select 1+2 -- Comment in line
 72           +3 as name
 73           , 4+5 as value
 74         
 75         )
 76         ;

 TERADATA_0: Prepared: on connection 0
 select 1+2 -- Comment in line +3 as name , 4+5 as value

So it runs without error, but it just returns a single value of 3 since that is the sum of 1 and 2.

Also note that in SAS -- has no special meaning. It is just two - next to each other. Try this query and you will see that X has the same value as AGE.

select name,age,--age as x
from sashelp.class
;

Either use your source code with the end of line comments to create a stored process in your database that you can call from SAS or remove the end of line comments before including them in your PROC SQL code.