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;
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;
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;
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.
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.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.