topic Re: Execute statement for MS SQL Server in SAS Programming

Execute statement for MS SQL Server

monona — Fri, 01 Mar 2019 21:59:19 GMT

Hello. I made a connection to ms sql server, successfully set up a table using execute statement, and this directly updated to the ms sql server.

Now, I would like to insert sas dataset( let say work.abc) into there (columns attribute are exactly like that:chr, bp, beta, p_value,trait). How can I do that?

Re: Execute statement for MS SQL Server

mwhitaker — Fri, 01 Mar 2019 22:23:41 GMT

Use a Libname Statement to create another connection, It looks like you are writing to a dbo schema.

Libname dbo odbc Required='....' schema=dbo;

Then a Proc Append (The Data Set will not work, because it will want to delete the current table)

Proc Append base=dbo.ukbb_statin data=[your table] [other options as needed];

you need to get the data over there somehow, and this seems the cleanest to me.

That last Execute statement will not be used. I would try just the Libname and Proc Append,, and see if the table is created by SAS.

Good Luck

Mike

Re: Execute statement for MS SQL Server

SASKiwi — Fri, 01 Mar 2019 22:27:45 GMT

You can't use the EXECUTE statement to directly load SAS tables as SAS data isn't available within SQL Server. I find PROC DATASETS works well. BTW SQL Server will create the BASE table if it doesn't already exist.

libname sqlsrvr ODBC noprompt="<put your connection string here>";

proc datasets library = sqlsrvr;
  append base = sqlsrvr.MyTable data = work.MySASTable;
run;
quit;

Re: Execute statement for MS SQL Server

monona — Fri, 01 Mar 2019 23:07:41 GMT

Hello, this works without error and generated a table under the connected library, but this doesn't push to sql server. I am attaching my code below.

proc sql;
   connect to odbc as db (required="driver=sql server native client 11.0;
							 	    seerver=MYSERVER;
								    Trusted_Connection=Yes;
								    Database=MYDB;");

	execute(drop table mvp_lipid) by db;
	execute(create table mvp_lipid (
		    chr varchar(2),
		    bp numeric(18),
		    beta float,
		    p_value float,
		    trait varchar(20) 
			)) by db;
quit;


libname db odbc noprompt = "server=MYSERVER;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes" DATABASE = MYDB schema = dbo;

proc datasets library = db;
	append base = db.mvp_lipid data=sasdata.mvp_lipid;
run;

Re: Execute statement for MS SQL Server

SASKiwi — Fri, 01 Mar 2019 23:36:30 GMT

Try this. If it works and prints 10 rows of output then SAS has created an SQL Server table not where you are expecting.

proc print data = db.mvp_lipid (obs = 10);
run;

Re: Execute statement for MS SQL Server

monona — Sat, 02 Mar 2019 00:59:28 GMT

Your code does work, and db.mvp_lipd has created in SAS, but that doesn't affect ms sql server...T_T

The empty mvp_lipid shown in sql server is created by the following code.

proc sql;
   connect to odbc as db (required="driver=sql server native client 11.0;
							 	    server=MYSERVER;
								    Trusted_Connection=Yes;
								    Database=MYDB;");

	execute(drop table mvp_lipid) by db;
	execute(create table mvp_lipid (
		    chr varchar(2),
		    bp numeric(18),
		    beta float,
		    p_value float,
		    trait varchar(20) 
			)) by db;
	
	disconnect from db;   
quit;

Re: Execute statement for MS SQL Server

SASKiwi — Sat, 02 Mar 2019 01:08:39 GMT

The DB libref points to an SQL Server table somewhere as the PROC PRINT worked. The question is where is the "DB" table hiding??

Try this in SQL Server Management Studio:

select * from [MYDB].[dbo].[mvp_lipid]

Re: Execute statement for MS SQL Server

monona — Sat, 02 Mar 2019 01:08:53 GMT

My question is why this is reflected to sql server, BUT the second chunk is not relfected.

proc sql;
   connect to odbc as db (required="driver=sql server native client 11.0;
							 	    server=MYSERVER;
								    Trusted_Connection=Yes;
								    Database=MYDB;");

	execute(drop table mvp_lipid) by db;
	execute(create table mvp_lipid (
		    chr varchar(2),
		    bp numeric(18),
		    beta float,
		    p_value float,
		    trait varchar(20) 
			)) by db;
	
	disconnect from db;   
quit;

libname db odbc noprompt = "server=MYSERVER;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes" DATABASE = MYDB schema = dbo;

proc datasets library = db;
	append base = db.ukbb_statin data=sasdata.ukbb_statin;
run;

Re: Execute statement for MS SQL Server

monona — Sat, 02 Mar 2019 01:15:55 GMT

same result as shown above. Empty.

Re: Execute statement for MS SQL Server

monona — Sat, 02 Mar 2019 01:16:51 GMT

My question is simply why the second chunk of code(about proc append) doesn't create any table in the corresponding table at ms sql server, but the first one does.

Re: Execute statement for MS SQL Server

Patrick — Sat, 02 Mar 2019 01:18:28 GMT

@monona

You don't specify a schema dbo here:

   connect to odbc as db (required="driver=sql server native client 11.0;
							 	    server=MYSERVER;
								    Trusted_Connection=Yes;
								    Database=MYDB;");

But you specify dbo here:

libname db odbc noprompt = "server=MYSERVER;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes" 
DATABASE = MYDB schema = dbo;

Given that the Proc Append as such appears to work: Are you sure you're looking in the correct schema for the table with data?

May be also add the following options before your Proc Append statement which will give you a bit more info in the SAS log of what's happening.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

Re: Execute statement for MS SQL Server

monona — Sat, 02 Mar 2019 03:13:47 GMT

my problem is

libname db odbc noprompt = "server=MYSERVER;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes"
DATABASE = MYDB schema = dbo;

this doesn't work but the first one works....TT

Re: Execute statement for MS SQL Server

Patrick — Sat, 02 Mar 2019 07:01:35 GMT

@monona

Your explicit pass-through SQL statement runs fully in-database and creates a table.

The Proc Append loads data from SAS to a database so that's involves also a data transfer.

Does "not work" mean you get an error in the SAS log or does it mean you don't see data in the SQL table.

From what you wrote so far I understood that the Proc Append executes without errors. If so then the code highly likely loads data into the database - and it also creates the table if it doesn't exist already.

So.... Given that you don't use an explicit schema name for the connection of the pass-through SQL: Are you really sure that the default schema is DBO which you're using for the libname statement? And are you really sure that you're looking in schema DBO when checking if data has been loaded into the database? Or are you looking in the default schema where you've created the table structure only?