BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
smallavolu
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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;

View solution in original post

10 REPLIES 10
SASKiwi
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

Thanks, but still no luck.

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

SASKiwi
PROC Star

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
Calcite | Level 5

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
Super User

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

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
smallavolu
Calcite | Level 5

Thanks Quentin and SASKiwi for all your help and tips. 

SASKiwi
PROC Star

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
Super User

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;

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 2527 views
  • 0 likes
  • 3 in conversation