A SAS Bug ?

[Ksharp]

I just found a problem when I am using SQL. I am trying to modify length of numeric variable. but found nothing changed even the LOG said modify successfully .

Is it a sas bug ?  SAS9.2

data have1;
length id 3;
input name : $2. x  $ id ;
cards;
NB sds 1
RN sdft 1
;
run;
proc sql;
alter table work.have1
modify id num(8);
quit;

653  data have1;

654  length id 3;

655  input name : $2. x  $ id ;

656  cards;

NOTE: The data set WORK.HAVE1 has 2 observations and 3 variables.

NOTE: DATA statement used (Total process time):

      real time          0.00 seconds

      cpu time            0.00 seconds

659  ;

660  run;

661  proc sql;

662  alter table work.have1

663  modify id num(8);

NOTE: Table WORK.HAVE1 has been modified, with 3 columns.

664  quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time          0.00 seconds

      cpu time            0.00 seconds

Xia Keshan

[Haikuo · Accepted solution]

This is a known limitation already addressed in SAS Docs.

"Note:   You cannot change the length of a numeric column with the ALTER TABLE statement. Use the DATA step instead."

Base SAS(R) 9.2 Procedures Guide

Regards,

Haikuo

[jakarman]

Although it is documented that it is not working I have to agree with Xia it is a bug.
We have the ANSI-Sql that SAS makes the statement it would be implemented. The syntax is documented that way.
And than claiming it is not working..... buggy approach.

When working in bigger organizations you will get the segregation of duties. SQL has several area's being spilt up
DML Data Manipulation Language that is the part that is normally open

DDL Data Definition Language  this is the part for the applicative DBA with some parts of MDL

MDL Metadata Definition Language is the higher level concepts where a master/system DBA get his role.
This segregation can be important but is neglected with SAS.

[Ksharp]

Thanks HaiKuo and Jaap. That is what I am looking for .

But as Jaap said, I think at least LOG should told user numeric variable length can't be changed , not show us modify has been done successfully which would mislead other sas user like me .

But interesting thing is actually SQL can change the length of numeric variable if you just want append them all together by UNION . Thanks RW9 .

data have1;
length id 3;
input name : $2. x  $ id ;
cards;
NB sds 1
RN sdft 1
;
run;
data have2;
input name : $2. x  $ id ;
cards;
NB sds 1
RN sdft 1
;
run;
proc sql;
create table want as
 select * from have1 
  outer union corr
 select * from have2;
quit;

Xia Keshan