BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sathish_jammy
Lapis Lazuli | Level 10

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
ChrisBrooks
Ammonite | Level 13

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
Lapis Lazuli | Level 10

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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