BookmarkSubscribeRSS Feed
Malarkey
Obsidian | Level 7

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;

7 REPLIES 7
ballardw
Super User

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.

Malarkey
Obsidian | Level 7

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.

ballardw
Super User

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.

SASKiwi
PROC Star

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

Patrick
Opal | Level 21

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"

Malarkey
Obsidian | Level 7

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?

Tom
Super User Tom
Super User

proc sql ;

connect to odbc .... ;

execute (

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

) by odbc ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3656 views
  • 3 likes
  • 5 in conversation