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.
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;
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.
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.