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
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
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.