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;
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.
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.