Create SQL Server table from SAS program

[smallavolu]

Hi,

I am new to SAS programming, trying to create new SQL Server table and then insert data from SAS dataset.

Here are the steps I have used,

1. Create a ODBC data source (Microsoft SQL Server Native Client Version 11.0), as 'XXX_DSN'.

2. Below is the SAS program I have used.

LIBNAME FA ODBC DSN='XXX_DSN' schema=dbo datasrc="XXX" user=uid password=pwd;

PROC SQL;
CREATE TABLE FA.SM_TEST1 (
ID varchar(12)
);
quit;

I end up getting error as below.

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL
Server]CREATE TABLE permission denied in database 'YYYYDataMart'..

Please help.

Thanks,

Shirish

[SASKiwi]

You don't have the CREATE TABLE permission in the database you are trying to write to. You will need to request this from your SQL Server database administrator.

[smallavolu]

Thanks SASKiwi. 

But, which is used as user mentioned in LIBNAME "uid" as "user=uid" does have rights to create the tables. and I did cross check by creating table SSMS.

Thanks

[SASKiwi]

In that case I suggest you use SQL Passthru to get better control over what SQL Server is doing:

LIBNAME FA ODBC DSN='XXX_DSN' schema=dbo datasrc="XXX" user=uid password=pwd;

PROC SQL;
connect using FA;
execute(
CREATE TABLE MyServer.Mydatabase.dbo.FA.SM_TEST1 (
ID varchar(12)
) by FA;
quit;

[smallavolu]

Thanks, but still no luck.

This time code does not throw any error. But, no table is created.

[SASKiwi · Accepted solution]

Please post the complete SAS log of your program.

EDIT - there is a missing bracket in the code I posted. Try this:

LIBNAME FA ODBC DSN='XXX_DSN' schema=dbo datasrc="XXX" user=uid password=pwd;

PROC SQL;
connect using FA;
execute(
CREATE TABLE MyServer.Mydatabase.dbo.FA.SM_TEST1 (
ID varchar(12)
)
) by FA;
quit;

[smallavolu]

Yes the missing bracket was the issue. But, surprised the program did not throw any error.

Any I just used MyServer.Mydatabase.dbo.SM_TEST1 .

One follow-up question, how to use pass thru SQL when we need to use insert or update statements. As how can use SAS datasets in SQL pass thru statements.

Thanks,

Shirish

[Quentin]

---

@smallavolu wrote:

One follow-up question, how to use pass thru SQL when we need to use insert or update statements.

Just like in SSMS.  Often I will write SQL code in SSMS and get it working there, then paste it into the pass-thru shell.

And how can use SAS datasets in SQL pass thru statements.

I'm not quite sure what you mean by this, but I think the answer is "you can't".  The point of explicit pass through is that your SQL code runs on SQL server, it is not run in SAS.  So your SQL server instance can't read a SAS dataset.  One option is to load the data from your SAS dataset to a SQL server table (could be temporary).  Then your explicit pass-through SQL code use that table.

[smallavolu]

Thanks Quentin and SASKiwi for all your help and tips. 

[SASKiwi]

When loading SAS datasets I usually use PROC DATASETS and the APPEND statement as it is simple and efficient. I tend to avoid updates, and see if I can instead use deletes and inserts - this is what APPEND does.

[Quentin]

I would check the log again, there should be an error if the table is not being created.

You can also try:

LIBNAME FA ODBC DSN='XXX_DSN' schema=dbo datasrc="XXX" user=uid password=pwd;

proc append base=fa.class data=sashelp.class;
run;

Assuming there is no table in your database named class, it should be created, and then the 19 records from sashelp.class should be inserted into it.  And if you run it again, it should insert the same 19 records again (so you'll have 38 records).

Agree with @SASKiwi  that using explicit pass through is also a great approach.  You said you have run the create table code in SQL Server Management Studio and it works?  Then you should be able to paste that code into the pass-through shell:

LIBNAME FA ODBC DSN='XXX_DSN' schema=dbo datasrc="XXX" user=uid password=pwd;

PROC SQL;
connect using FA;
execute(
/*your working SQL code from SSMS here*/
) by FA;
quit;

That first create table permission error is pretty clear.  I would double-check that when you connect via SSMS, you are the same user as you pass in the SAS session.  And double check your DSN is pointing to the right database.

Wait, after typing all that I just noticed your LIBNAME statement has both DSN= and DATAsrc=, with different values.  DSN is an alias for DATASRC.  You don't want both of these.  As written, I don't know if you're connecting to XXX_DSN or XXX.  The log might tell you.

LIBNAME FA ODBC DSN='XXX_DSN' schema=dbo datasrc="XXX" user=uid password=pwd;