Altering column in PROC SQL

[Sathish_jammy]

Hi,

Can any one please assist me to solve the given code.

proc sql;
  create table proj_s.ddrug_samp as
  select mno,ddrug11,ddrug12,ddrug13 from proj_s.durat1yr_fbs_yr;
  quit;

OUTPUT : Done 

proc sql;
alter table proj_s.ddrug_samp add ddrugx char(10);
quit;

ERROR:
35   proc sql;
36   alter table proj_s.ddrug_samp add ddrugx char(10);
ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ODBC engine.
ERROR: View PROJ_S.ddrug_samp cannot be altered.
NOTE: The SAS System stopped processing this step because of errors.

[ChrisBrooks]

The SAS/ACCESS libname engine for RDBMS data sources doesn't support the ALTER TABLE statement. You will need to use SQL pass-through with the correct syntax for the RDBMS your data is held in.

[Sathish_jammy]

Thanks for your guidance. Your code works well.

( RENAMED -  LIBNAME proj_s as Projs )

This is the code i used to connect with database.

libname projs odbc noprompt= "Driver=SQL Server;Server=192.168.1.3;Database=Project;UID=IT;PWD=***;";	

As you said data source doesn't support the ALTER STATEMENT means,

In further i need new columns in this table. Then how do i proceed with alter statement.

[RW9]

You need to pass through, e.g.:

proc sql;
  connect to db (path=...);
  execute by db ('alter table ...;');
  disconnect from db;
quit;

So much like writing the alter table statement on your database.

[RW9 · Accepted solution]

What is library reference proj_s?  It sounds to me like a database link to get that kind of error.  Doing this on a dataset works fine:

data class;
  set sashelp.class;
run;

proc sql;
  alter table class add ddrugx char(10);
quit;

Why not just:

proc sql;
  create table proj_s.ddrug_samp as
  select mno,ddrug11,ddrug12,ddrug13,"" as ddrugx length=10 from proj_s.durat1yr_fbs_yr;
quit;