Proc SQL Connect to ODBC

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Proc SQL Connect to ODBC

I'm trying to create a table in SQLServer using Proc SQL and execute then select from a join of 2 other tables.  I am getting a syntax error  -any help would be appreciated

 

2470 connect to odbc(datasrc="FMG Reporting NT Auth");
2471 execute
2472 (create table BIAnalysts.testit as
2473 select
2474 a.cust_edp_id,
2475 b.aff_any
2476
2477 from dbo.FMG_Mkt_CustSalesHeader a,
2478 dbo.FMG_WebOrderMarkers b
2479 where a.SalesOrdHeaderKey > 0
2480 and a.year_ in ('2014','2015')
2481 and a.sales_ord_nr = b.ecometryorderno
2482 ) by odbc;
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.
2483
2484 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

 

 


Accepted Solutions
Solution
‎02-02-2016 07:04 PM
Super User
Posts: 3,235

Re: Proc SQL Connect to ODBC

I did some Googling and I think you need to do something like this rather than CREATE TABLE AS.....

 

Select * into new_table  from  old_table

If you have access to SQL Server Management Studio I'd get the query working there first then paste it into the SAS EXECUTE statement.

View solution in original post


All Replies
Super User
Posts: 3,235

Re: Proc SQL Connect to ODBC

[ Edited ]

This is the correct syntax to create a SAS table - does this work for you?

proc sql;
connect to odbc(datasrc="FMG Reporting NT Auth");
create table BIAnalysts as select *
from connection to ODBC as
 (select a.cust_edp_id,
            b.aff_any
  from dbo.FMG_Mkt_CustSalesHeader a,
  etc...
 );
quit;

 

To create an SQL Server table:

 

proc sql;
connect to odbc(datasrc="FMG Reporting NT Auth");
execute(create table BIAnalysts as 
 select a.cust_edp_id,
            b.aff_any
  from dbo.FMG_Mkt_CustSalesHeader a,
  etc...
 ) by ODBC;
quit;
Contributor
Posts: 43

Re: Proc SQL Connect to ODBC

BIAnalysts is another schema with the database.

 

I want to create a table named testit within in a schema that is different from the where the select statment is -  Is that even possible?  

Super User
Posts: 3,235

Re: Proc SQL Connect to ODBC

Try this then:

 

proc sql;
connect to odbc(datasrc="FMG Reporting NT Auth");
execute(create table [BIAnalysts].[testit] as 
 select a.cust_edp_id,
            b.aff_any
  from dbo.FMG_Mkt_CustSalesHeader a,
  etc...
 ) by ODBC;
quit;
Contributor
Posts: 43

Re: Proc SQL Connect to ODBC

getting the same syntax error for keyword 'as'

 

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.

Solution
‎02-02-2016 07:04 PM
Super User
Posts: 3,235

Re: Proc SQL Connect to ODBC

I did some Googling and I think you need to do something like this rather than CREATE TABLE AS.....

 

Select * into new_table  from  old_table

If you have access to SQL Server Management Studio I'd get the query working there first then paste it into the SAS EXECUTE statement.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 594 views
  • 0 likes
  • 2 in conversation