In the PROC SQL procedure, enable the ALTER TABLE/MODIFY statement to convert character variables into numeric variables and vice versa.
A typical requirement of an operation of this type, when it does actually exist, is that the column in question be 100% null and the modify basically acts as a shortcut to dropping and re-adding the column in the new type. If this the behavior you are suggesting or do you expect the operation to actually cast the values between the types?
Yes, I would like for the values to be recast. I can add columns, populate them with the data in the old columns and then drop the old columns. However, that misorders the columns, which is a problem for what I am doing, and leaves the added columns with their new names (unless I have overlooked how to rename the columns).
What you want is best done in a data step. Is a similar conversion part of ANSI SQL?
When I suggested this, I was thinking of an ALTER TABLE/MODIFY statement that would simply change the format from character to numeric and vice versa. I must confess that I did not take the complexity of this operation into account. However, why couldn't the original values be converted and written to an intermediate table, the values in the original column deleted, and the converted values written back to the original column? Or, else, the converted values could simply overwrite the original values. This operation would resemble how the conversion is performed on a spreadsheet. There, the original character values are converted into numeric values in a separate column and then copied back to the original column. The spreadsheet model also suggests that an UPDATE statement could be used to replace the values in the original column with values in the new format. For instance, if Year is a character variable: SET Year = Input(Year,4.), or perhaps a function could be created, something like: SET Year = Convert(Year, 4.).
Recasting implies a rewrite of the data set, because the structure in the record changes (numbers are 3 to 8 bytes in length, depending on the wanted precision; a large number will need more than 8 characters to write out)
The rewrite can be done in SAS SQL with the put() and input() functions:
x = "1";
create table test2 as
select input(x,1.) as x from test1;
create table test3 as
select put(x,1.) as x from test2;
Regarding KurtBremser's suggestion, your code works but when I try:
select input(YYYY,4.) as YYYY
I get an error message that the components are of different data types. Is that because you didn't explicitly assign a format to x in the data step?
I ran the code again and selected YYYY as Year and a number of other variables into CR_Temp from the original dataset. Then I selected Input(Year,4.) as Year and the other variables into CR_Values from CR_Temp and I got a numeric Year. A bit convoluted but it works. I am surprised that the 2nd SELECT statement changes the data type of Year. I would have thought that once a column was a character column nothing would change it.
"select function(variable) as variable" will always create a new variable with new attributes.
Unless you try
select variable, function(variable) as variable
where the SQL procedure will immediately complain about variable already being inlcuded in the dataset.
The "components are of different data types" most likely does not come from the input function, but from some other part of the select (where condition?)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.