BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jei
Quartz | Level 8 jei
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

4 REPLIES 4
SASKiwi
PROC Star

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;

 

jei
Quartz | Level 8 jei
Quartz | Level 8

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.

SASKiwi
PROC Star

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.

jei
Quartz | Level 8 jei
Quartz | Level 8
Okay. I did your suggestion. It worked!

Thank you so much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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