BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Hi everyone,

I am trying to update the table named "test" in the in the SQL database. I have tried the libname option as;

 

libname PIlibt odbc noprompt="uid=myuid;

pwd=mypassword;

dsn=mydsn;"

stringdates=yes;

PROC SQL;

ALTER TABLE PILIBT.TEST

ADD MYVAR NUM

DROP AGE;

QUIT;

 

This statemnt gave me an error message :

ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ODBC engine.

ERROR: View PILIBT.TEST cannot be altered.

 

Next I am trying to use pass through facility option using the the code below and my code is not correct.

 

PROC SQL;

CONNECT TO ODBC AS MYCON

(DSN=mydsn USER=myuid PASSWORD=mypassword);

ALTER TABLE TEST

ADD NEWVAR NUM

DROP AGE;

DISCONNECT FROM MYCON;

QUIT;

 

could you help me fixing this codes to update my table.

 

Thanks for your support.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

I believe the following SAS note applies also for SQL Server http://support.sas.com/kb/37/015.html

 

Your pass-through SQL code needs to be in an EXECUTE() block as shown in the SAS Note.

 

The pass-through SQL code must be valid SQL syntax in the flavor of the database.

 

View solution in original post

10 REPLIES 10
Reeza
Super User
When you use Pass Through your code needs to comply with the SQL requirements of your DB. What type of DB are you on, Oracle SQL, MS SQL, etc? The SQL would need to match that system.

I am in MS SQL

Hi Reeza,

I am updating in the MS SQL. It does not let me add or drop the columns in the the existing tables in the database.

 

 

 

Thanks,

Ram

Reeza
Super User
Are you using something like the following. Please post your full code that doesn't work. Also, verify you have permissions to add/drop columns.

Alter table my_schema.my_table ADD column_b VARCHAR(20) NULL;
LinusH
Tourmaline | Level 20

What is the use case for altering a SQL Server table from SAS?

SAS programs is normally not a DBA tool, unless you try to build it into some kind of application.

The easiest should be to use SQL Server Management Studio.

Data never sleeps
Patrick
Opal | Level 21

I believe the following SAS note applies also for SQL Server http://support.sas.com/kb/37/015.html

 

Your pass-through SQL code needs to be in an EXECUTE() block as shown in the SAS Note.

 

The pass-through SQL code must be valid SQL syntax in the flavor of the database.

 

Hi Patrick,

Thank you for the exec options. The code that you provided works when creating additional columns in the table, but it does not work when i try to drop the columns from the existing table.

 

When Submitted the code below error was generated

 

PROC SQL;

CONNECT TO ODBC

(DSN=mydsn USER=myuid PASSWORD=mypassword);

EXEC (ALTER TABLE DBO.TEST

drop Age) BY ODBC;

DISCONNECT FROM ODBC;

QUIT;

 

ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]'Age' is not a

constraint. : [Microsoft][ODBC SQL Server Driver][SQL Server]Could not drop constraint.

See previous errors.

 

Could you help me fix this.

 

Thanks

 

 

 

Patrick
Opal | Level 21

Looks like your SQL syntax is incorrect for dropping a column. The error you get 'Age' is not a constraint indicates that this would be valid syntax for dropping a constraint.

 

I believe the valid syntax would be: 

ALTER TABLE DBO.TEST

drop column Age

 

LinusH
Tourmaline | Level 20

We are ready to help, and share knowledge. But this is not a free of charge ordering service. So please phrase differently from now on ("Could you help me fix this").

Second, this is not SAS syntax, it's SQL Server, and something you clearly could have googled yourself.

Now that@Patrick had the kindness to answer your inquiry, make sure to mark it as a solution.

 

Br

Linus

 

Data never sleeps

Thank you so much Patrick

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 5526 views
  • 1 like
  • 4 in conversation