Help using Base SAS procedures

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

Reply
Contributor
Posts: 27

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

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;

Super User
Posts: 11,343

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

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.

Contributor
Posts: 27

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

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.

Super User
Posts: 11,343

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

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.

Super User
Posts: 3,256

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

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  */

Respected Advisor
Posts: 4,173

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

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"

Contributor
Posts: 27

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

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?

Super User
Super User
Posts: 7,061

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

proc sql ;

connect to odbc .... ;

execute (

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

) by odbc ;

Ask a Question
Discussion stats
  • 7 replies
  • 932 views
  • 3 likes
  • 5 in conversation