Execute statement for MS SQL Server

[monona]

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?

[mwhitaker]

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 

[SASKiwi · Accepted solution]

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; 

[monona]

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;

[SASKiwi]

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;

[monona]

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;

[SASKiwi]

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]

[monona]

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;

[Patrick]

@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; 

[monona]

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

[Patrick]

@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?

[monona]

same result as shown above. Empty.

[monona]

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.