topic Re: PROC SQL Pass Through on Multiple Databases in SAS Programming

PROC SQL Pass Through on Multiple Databases

jei — Wed, 13 Sep 2017 00:58:36 GMT

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;

Re: PROC SQL Pass Through on Multiple Databases

SASKiwi — Wed, 13 Sep 2017 01:16:47 GMT

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;

Re: PROC SQL Pass Through on Multiple Databases

jei — Wed, 13 Sep 2017 01:42:43 GMT

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.

Re: PROC SQL Pass Through on Multiple Databases

SASKiwi — Wed, 13 Sep 2017 02:11:15 GMT

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.

Re: PROC SQL Pass Through on Multiple Databases

jei — Wed, 13 Sep 2017 03:30:37 GMT

Okay. I did your suggestion. It worked!

Thank you so much!