DATA Step, Macro, Functions and more

Altering column in PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Altering column in PROC SQL

[ Edited ]

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.

 

 


Accepted Solutions
Solution
4 weeks ago
Super User
Super User
Posts: 9,606

Re: Altering column in PROC SQL

Posted in reply to Sathish_jammy

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;

View solution in original post


All Replies
Valued Guide
Posts: 595

Re: Altering column in PROC SQL

Posted in reply to Sathish_jammy

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.

Contributor
Posts: 33

Re: Altering column in PROC SQL

[ Edited ]
Posted in reply to ChrisBrooks

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.

 

 

Super User
Super User
Posts: 9,606

Re: Altering column in PROC SQL

Posted in reply to Sathish_jammy

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.

Solution
4 weeks ago
Super User
Super User
Posts: 9,606

Re: Altering column in PROC SQL

Posted in reply to Sathish_jammy

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 99 views
  • 1 like
  • 3 in conversation