topic Re: NOTE: No rows were selected. When Running Pass-Through SQL in SAS Procedures

NOTE: No rows were selected. When Running Pass-Through SQL

Malarkey — Mon, 30 Mar 2015 20:26:54 GMT

Hi, All!

I am using the macro facility to generate pass-through SQL code and then call execute to execute the SQL. When running in SAS, the output table is not created but when I run the generated SQL in SQL Server Management Studio, the table is created. I've looked and looked and I can't figure out what the problem is. Anyone know? Thanks!

Generated SQL:

proc sql;

connect to odbc (dsn="QN1_WHSE_MyDatabase");

select * from connection to odbc

(

select some_column

,314 as a_constant

,another_column as column_alias

,'C' as another_constant

,column_to_test

,case

when column_to_test is not null

and column_to_test <> ''

then 1

else 0

end as dq_column_to_test

,1 as column_to_test_ct

<<<<More tests like above>>>>

into [WorkDb].[dbo].MyOutputTable

from [WorkDB].[dbo].MyDriverTable drvr

join TableToTest t1

on drvr.key_field = t1.key_field

)

;

quit;

Re: NOTE: No rows were selected. When Running Pass-Through SQL

ballardw — Mon, 30 Mar 2015 20:53:07 GMT

The most likely is the use of a feature in SQL server that SAS doesn't support or uses differently. I suspect the INTO doesn't work on SAS as the SAS INTO is designed to place values or list of values into macro variable not tables.

Re: NOTE: No rows were selected. When Running Pass-Through SQL

Malarkey — Mon, 30 Mar 2015 20:57:14 GMT

I was worried about that when I posted and am afraid you might be correct.

But I am using pass-through SQL and I thought that executed on the DBMS, not via SAS?

From the documentation:

In the SELECT statement (that is, the PROC SQL query) that you write, use the

SQL that is native to your DBMS. SAS/ACCESS passes the SQL

statements directly to the DBMS for processing. If the SQL syntax that you enter

is correct, the DBMS processes the statement and returns any results to SAS. If

the DBMS does not recognize the syntax that you enter, it returns an error that

appears in the SAS log.

Re: NOTE: No rows were selected. When Running Pass-Through SQL

ballardw — Mon, 30 Mar 2015 21:29:05 GMT

I don't have tools that do any pass-through so I'll have to bow out. If you aren't getting errors though I might look to see if the YRDIF fuction behave differently on the host system. So you might not get errors just unexpected results.

Re: NOTE: No rows were selected. When Running Pass-Through SQL

SASKiwi — Mon, 30 Mar 2015 21:58:07 GMT

Your SQL contains an INTO clause that means output will go into a DBMS table instead of into the SAS SELECT statement.

What happens if you comment out the INTO clause - do you get your data coming back to SAS?

/* into [WorkDb].[dbo].MyOutputTable */

Re: NOTE: No rows were selected. When Running Pass-Through SQL

Patrick — Mon, 30 Mar 2015 22:30:07 GMT

Same as wrote, get rid of the INTO clause which creates a DBMS table, add a CREATE TABLE on the SAS side.

proc sql;

connect to odbc (dsn="QN1_WHSE_MyDatabase");

CREATE TABLE WORK.WANT AS

select * from connection to odbc

(

select some_column

,314 as a_constant

,another_column as column_alias

,'C' as another_constant

,column_to_test

,case

when column_to_test is not null

and column_to_test <> ''

then 1

else 0

end as dq_column_to_test

,1 as column_to_test_ct

<<<<More tests like above>>>>

into [WorkDb].[dbo].MyOutputTable

from [WorkDB].[dbo].MyDriverTable drvr

join TableToTest t1

on drvr.key_field = t1.key_field

)

;

quit;

....or: If your goal is to create a DBMS table then have the pass-through code in an EXECUTE block instead of "select ... from connection"

Re: NOTE: No rows were selected. When Running Pass-Through SQL

Malarkey — Mon, 30 Mar 2015 23:43:03 GMT

Hi, Patrick!

Yes, I do want the output in a DBMS table - there's not enough space on my local machine to hold some of the files I'll be building. When you say, "execute block," what is it that you mean?

Re: NOTE: No rows were selected. When Running Pass-Through SQL

Tom — Mon, 30 Mar 2015 23:51:12 GMT

proc sql ;

connect to odbc .... ;

execute (

<<< put your pass through code here >>>

) by odbc ;