CODEWITHSUNDEEP
sas
Frank
Frank

Reputation: 720

SAS ALTER TABLE MODIFY Length

Suppose I have in SAS someTable with a column someColumn of type Character.

I can adjust length, format, informat and label in the following way:

ALTER TABLE WORK.someTable
   MODIFY someColumn char(8) format=$CHAR6. informat=$CHAR6. label='abcdef'

But I doubt if this is the correct way for the following reasons:

Actually, I expected the following code to work:

ALTER TABLE WORK.someTable
   MODIFY someColumn length=8 format=$CHAR6. informat=$CHAR6. label='someLabel'

This code runs without errors nut does not change the length.

Question: What is the correct syntax to modify the length of a column using ALTER TABLE / MODIFY? (For arbitrary column type like character/numeric/date.)

Upvotes: 0

Views: 4650

Answers (1)

Tom
Tom

Reputation: 51566

The syntax for defining the altered variable ("column") is the same as the syntax PROC SQL uses for defining a variable. What the documentation calls "column-definition Component"

column data-type <column-modifier(s)>

That is why you use the SQL syntax, char(n) or num, for specifying the type. Note that SAS datasets only have two data types: fixed length character strings and floating point numbers. SAS will automatically convert any other SQL data-type into the proper one of those.

The limitations on altering the type are spelled out in the documentation:

Changing Column Attributes If a column is already in the table, then you can change the following column attributes by using the MODIFY clause: length, informat, format, and label. The values in a table are either truncated or padded with blanks (if character data) as necessary to meet the specified length attribute.

You cannot change a character column to numeric and vice versa. To change a column’s data type, drop the column and then add it (and its data) again, or use the DATA step.

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

Note that to make such changes to a dataset SAS will have to create a whole new dataset. So you might as well just write a data step to create the new dataset and then you will have full control.

Also be careful if you change the length of character variable to make sure that the attached FORMAT is still correct.

In your example you are changing the variable to be 8 bytes long, but are attaching a format that will only display the first 6 bytes.

In general it is best to not attach formats to character variables to avoid the confusion that type of mismatch can cause. Unfortunately there is no way to remove the attached format using PROC SQL. The best you could do is to set the format to $., that is without an explicit width. If you want to completely remove the format you will need to use a FORMAT statement in PROC DATASETS or a data step.

Upvotes: 4

Related Questions