An Idea Exchange for SAS software and services

Comments
by Trusted Advisor
on ‎03-13-2015 05:24 PM

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?

by Occasional Contributor cjmcgath_verizon_net
on ‎03-13-2015 05:34 PM

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).

by Super User
on ‎03-16-2015 06:04 AM

What you want is best done in a data step. Is a similar conversion part of ANSI SQL?

by Occasional Contributor cjmcgath_verizon_net
on ‎03-16-2015 12:14 PM

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.).

by Super User
on ‎03-16-2015 12:27 PM

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:

data test1;

x = "1";

run;

proc sql;

create table test2 as

select input(x,1.) as x from test1;

create table test3 as

select put(x,1.) as x from test2;

quit;

by Trusted Advisor
on ‎03-16-2015 12:28 PM

data class;

set sashelp.class(rename=(weight=weight_n));

weight=put(weight_n,best. -l);

drop weight_n;

run;

by Occasional Contributor cjmcgath_verizon_net
on ‎03-16-2015 01:41 PM

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?

by Occasional Contributor cjmcgath_verizon_net
on ‎03-16-2015 02:11 PM

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.

by Super User
on ‎03-17-2015 08:11 AM

"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.

by Super User
on ‎03-17-2015 08:19 AM

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?)

Idea Statuses
Top Liked Authors