DATA Step, Macro, Functions and more

PROC SQL Pass Through on Multiple Databases

Accepted Solution Solved
Reply
Contributor jei
Contributor
Posts: 74
Accepted Solution

PROC SQL Pass Through on Multiple Databases

Hi,

 

I would like to create SAS dataset from SQL tables using PROC SQL Pass through. I am submitting the following command but getting the errors. May I know if it is possible to create SAS Dataset form multiple DB on SQL Server using the Proc SQL Pass through?

 

Please refer below.

 

SAS Code:

 

/*Get Lot Info using PROC SQL Pass-Through*/
proc sql;

connect to SQLSVR as A (Datasrc=dload USER=user PASSWORD='password');
connect to SQLSVR as B (Datasrc=OPS User=user PASSWORD='password');

/*Explicit Pass-Through with SELECT*/
create table yld.Tmp_Mvmt_Dtls as
select
ID, OperationNumber,Code,
Quantity, Status, Station , Description , Category,
chrAssyYieldFlag , FlagMeasure

from connection to SQLSVR (A B)
(
select a.ID, a.OperationNumber,a.ode,
a.Quantity,
, b.Status, c.Station , c.Description , c.Category
, c.chrAssyYieldFlag , b.FlagMeasure
FROM [dload].[Transaction] a with (nolock)
left join [sas].[Operations] b with (nolock) on a.OperationNumber = b.OperNumbers
left join [sas].[Stations] c with (nolock ) on b.StationID = c.StationIDs
);
;
%put %sqlxrc &sqlxmsg; *SQL Server Query return code and message;
disconnect from SQLSVR;
quit;

 

LOG:

 

/*Get Lot Info using PROC SQL Pass-Through*/
825 proc sql;

826
827 connect to SQLSVR as A (Datasrc=dload USER=user PASSWORD='password');

828 connect to SQLSVR as B (Datasrc=OPS User=user PASSWORD='password');

831
832 /*Explicit Pass-Through with SELECT*/
833 create table yld.Tmp_Mvmt_Dtls as
834 select
835 ID, OperationNumber,Code,
836 Quantity, Status, Station , Description , Category,
837 chrAssyYieldFlag , FlagMeasure
841
842 from connection to SQLSVR (A B)
843 (
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: ',', EXCEPT, GROUP, HAVING, INTERSECT,
JOIN, ORDER, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

844 select a.ID, a.OperationNumber,a.ode,
845 a.Quantity,
846 , b.Status, c.Station , c.Description , c.Category
847 , c.chrAssyYieldFlag , b.FlagMeasure
848 FROM [dload].[Transaction] a with (nolock)
849 left join [sas].[Operations] b with (nolock) on a.OperationNumber = b.OperNumbers
850 left join [sas].[Stations] c with (nolock ) on b.StationID = c.StationIDs
851 );
852 ;
853 %put %sqlxrc &sqlxmsg; *SQL Server Query return code and message;
WARNING: Apparent invocation of macro SQLXRC not resolved.
%sqlxrc
854 disconnect from SQLSVR;
ERROR: Connection to the SQLSVR DBMS does not exist.
855 quit;


Accepted Solutions
Solution
‎09-12-2017 11:30 PM
Super User
Posts: 3,254

Re: PROC SQL Pass Through on Multiple Databases

[ Edited ]

Yes it is. You need to connect directly to your SQL Server servername in one connection only then refer to your tables including the database name, schema name and table name like so (using ODBC as an example):

 

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable]
   )
  ;
  disconnect from odbc;
quit;

 

View solution in original post


All Replies
Solution
‎09-12-2017 11:30 PM
Super User
Posts: 3,254

Re: PROC SQL Pass Through on Multiple Databases

[ Edited ]

Yes it is. You need to connect directly to your SQL Server servername in one connection only then refer to your tables including the database name, schema name and table name like so (using ODBC as an example):

 

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable]
   )
  ;
  disconnect from odbc;
quit;

 

Contributor jei
Contributor
Posts: 74

Re: PROC SQL Pass Through on Multiple Databases

Hi @SASKiwi,

 

Our SAS/Access is not ODBC byt SAS/Access to Microsoft SQL Server.

 

That is when I tried the connection to ODBC I'm getting the error below.

 

ERROR: The ODBC engine cannot be found.
ERROR: A Connection to the ODBC DBMS is not currently supported, or is not installed at your site.
35 ;

 

ERROR: Connection to the ODBC DBMS does not exist.

Super User
Posts: 3,254

Re: PROC SQL Pass Through on Multiple Databases

I wasn't suggesting you try ODBC. Use your SQLSVR connection to just connect to the SQL Server itself and not a particular database, then try the type of query I put in my previous post.

Contributor jei
Contributor
Posts: 74

Re: PROC SQL Pass Through on Multiple Databases

Okay. I did your suggestion. It worked!

Thank you so much!
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 142 views
  • 0 likes
  • 2 in conversation