BookmarkSubscribeRSS Feed
mcook
Quartz | Level 8

I have connected to a database on SQL Server Management Studio on my local machine, with SQL Express,  with the following ODBC code.  

 

  libname MattsLib odbc noprompt="driver=ODBC Driver 11 for SQL Server;
                             server=<my local machine ID>\SQLEXPRESS;
                             database=TestDB;
                             Trusted_Connection=yes"
                  schema=DBO;

This connects as expected, but in trying to figure out how to use "alter table" in SAS to alter SQL tables in SSMS, I ran this following code to create a table.  

 

CREATE TABLE dbo.account
( account_id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_account PRIMARY KEY CLUSTERED,
  account_name VARCHAR(100) NOT NULL,
  account_start_date DATE NOT NULL,
  account_address VARCHAR(1000) NOT NULL,
  account_type VARCHAR(10) NOT NULL,
  account_create_timestamp DATETIME NOT NULL,
    account_notes VARCHAR(500) NULL,
  is_active BIT NOT NULL);

It ran with no errors, warnings or notes... But where did it create the table to?  I am unable to locate it.  It is not in any database in SSMS that i can see (Particularly not in TestDB where i was expecting it)  And it is not in any library in my SAS session. 

 

Any suggestions?

 

Note:  i copied and pasted that create table code from here: https://www.sqlshack.com/methods-to-insert-data-into-sql-server/ 

3 REPLIES 3
JBailey
Barite | Level 11

Hi @mcook 

 

Can you show the actual SAS code you used to connect and run the CREATE TABLE statement? 

The following options will show the exact SQL that SAS is sending to the database:

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;

Rerun the job and include the resultant SAS log in this thread. If the table does exist, your SAS log will have an error stating that.

 

Good luck,

Jeff

 

 

 

mcook
Quartz | Level 8

I'm just a fool.  Ignore this question.  after working in sql for a few days, i forgot to start with proc sql in SAS.  😕

JBailey
Barite | Level 11

Hi @mcook 

 

Don't worry about it and welcome to the club. Many of us are members. I join this club at least once a day.

 

Best wishes,
Jeff

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 832 views
  • 5 likes
  • 2 in conversation