topic Re: A SAS Bug ? in SAS Procedures

A SAS Bug ?

Ksharp — Sun, 01 Jun 2014 15:11:09 GMT

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

Re: A SAS Bug ?

Haikuo — Sun, 01 Jun 2014 18:16:29 GMT

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

Re: A SAS Bug ?

jakarman — Mon, 02 Jun 2014 05:24:46 GMT

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.

Re: A SAS Bug ?

Ksharp — Mon, 02 Jun 2014 07:02:23 GMT

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