topic Re: Altering column in PROC SQL in SAS Programming

Altering column in PROC SQL

Sathish_jammy — Thu, 28 Jun 2018 08:34:21 GMT

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.

Re: Altering column in PROC SQL

ChrisBrooks — Thu, 28 Jun 2018 08:36:43 GMT

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.

Re: Altering column in PROC SQL

RW9 — Thu, 28 Jun 2018 08:36:50 GMT

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;

Re: Altering column in PROC SQL

Sathish_jammy — Thu, 28 Jun 2018 09:20:08 GMT

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.

Re: Altering column in PROC SQL

RW9 — Thu, 28 Jun 2018 09:21:14 GMT

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.