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;
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.
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.
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.
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 */
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"
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?
proc sql ;
connect to odbc .... ;
execute (
<<< put your pass through code here >>>
) by odbc ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.