topic Re: Create SQL Server table from SAS program in New SAS User

Create SQL Server table from SAS program

smallavolu — Thu, 17 Nov 2022 21:02:05 GMT

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

Re: Create SQL Server table from SAS program

SASKiwi — Thu, 17 Nov 2022 21:15:28 GMT

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.

Re: Create SQL Server table from SAS program

smallavolu — Thu, 17 Nov 2022 21:27:40 GMT

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

Re: Create SQL Server table from SAS program

SASKiwi — Thu, 17 Nov 2022 22:20:30 GMT

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;

Re: Create SQL Server table from SAS program

smallavolu — Thu, 17 Nov 2022 23:01:02 GMT

Thanks, but still no luck.

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

Re: Create SQL Server table from SAS program

SASKiwi — Thu, 17 Nov 2022 23:12:05 GMT

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;

Re: Create SQL Server table from SAS program

Quentin — Thu, 17 Nov 2022 23:19:28 GMT

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;

Re: Create SQL Server table from SAS program

smallavolu — Fri, 18 Nov 2022 06:16:23 GMT

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

Re: Create SQL Server table from SAS program

Quentin — Fri, 18 Nov 2022 13:36:25 GMT

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

Re: Create SQL Server table from SAS program

smallavolu — Fri, 18 Nov 2022 15:19:54 GMT

Thanks Quentin and SASKiwi for all your help and tips.

Re: Create SQL Server table from SAS program

SASKiwi — Fri, 18 Nov 2022 22:43:28 GMT

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.