Convert Numeric Variables to Character
From sasCommunity
The task is to create a successor to a SAS data set, replacing each numeric variable in the original with a character variable. Here is a method which preserves variable order and variable attributes. First create a data set for the demonstration:
proc sql;
create table mixedtype as
select name label = 'First Name'
, sex format = $1.
, age label = 'Age as of 1 March 2008'
, height
, weight format = 6.2
from sashelp.class;
Result:
Age as
of 1
First March
Name Sex 2008 Height Weight
-----------------------------------------
Alfred M 14 69 112.50
Alice F 13 56.5 84.00
Barbara F 13 65.3 98.00
Carol F 14 62.8 102.50
Henry M 14 63.5 102.50
James M 12 57.3 83.00
Jane F 12 59.8 84.50
Janet F 15 62.5 112.50
Jeffrey M 13 62.5 84.00
John M 12 59 99.50
Joyce F 11 51.3 50.50
Judy F 14 64.3 90.00
Louise F 12 56.3 77.00
Mary F 15 66.5 112.00
Philip M 16 72 150.00
Robert M 12 64.8 128.00
Ronald M 15 67 133.00
Thomas M 11 57.5 85.00
William M 15 66.5 112.00
Next, this somewhat tricky query which uses DICTIONARY.COLUMNS as a source of metadata and creates the bulk of another query:
select case type
when 'num' then catx ( ' '
, 'left( put ('
, name
, ','
, case
when format is null then 'best12.'
else format
end
, ') ) as'
, name
, 'label ='
, quote( strip(label) )
)
when 'char' then name
else catx ( ' '
, quote('Error on type')
, 'as'
, name
)
end
into : selections separated by ' , '
from dictionary.columns
where libname='WORK' and memname='MIXEDTYPE';
To adapt for other tables change only the literals in the WHERE clause.
Result:
Name Sex left( put ( Age , best12. ) ) as Age label = "Age as of 1 March 2008" left( put ( Height , best12. ) ) as Height label = "" left( put ( Weight , 6.2 ) ) as Weight label = ""
The INTO clause takes these expressions and concatenates them into a comma-separated string which it stores in the macro variable SELECTIONS, which in turn is used in the statement which actually does the work:
create table allchar as select &selections from mixedtype; %symdel selections;
Result:
First Age as of 1 Name Sex March 2008 Height Weight ------------------------------------------------- Alfred M 14 69 112.50 Alice F 13 56.5 84.00 Barbara F 13 65.3 98.00 Carol F 14 62.8 102.50 Henry M 14 63.5 102.50 James M 12 57.3 83.00 Jane F 12 59.8 84.50 Janet F 15 62.5 112.50 Jeffrey M 13 62.5 84.00 John M 12 59 99.50 Joyce F 11 51.3 50.50 Judy F 14 64.3 90.00 Louise F 12 56.3 77.00 Mary F 15 66.5 112.00 Philip M 16 72 150.00 Robert M 12 64.8 128.00 Ronald M 15 67 133.00 Thomas M 11 57.5 85.00 William M 15 66.5 112.00
Another query against DICTIONARY.COLUMNS compares the attributes of the original and replacement variables:
select name format=$10.
, memname format=$10.
, varnum format=10.
, type format=$8.
, length format=6.
, format format=$8.
, label format=$30.
from dictionary.columns
where libname='WORK' and memname in ('ALLCHAR','MIXEDTYPE')
order by varnum, memname desc;
quit;
Result:
Column Column Member Number in Column Column Column Name Name Table Type Length Format Column Label -------------------------------------------------------------------------------------- Name MIXEDTYPE 1 char 8 First Name Name ALLCHAR 1 char 8 First Name Sex MIXEDTYPE 2 char 1 $1. Sex ALLCHAR 2 char 1 $1. Age MIXEDTYPE 3 num 8 Age as of 1 March 2008 Age ALLCHAR 3 char 12 Age as of 1 March 2008 Height MIXEDTYPE 4 num 8 Height ALLCHAR 4 char 12 Weight MIXEDTYPE 5 num 8 6.2 Weight ALLCHAR 5 char 6
This demonstrates that variable order and variable attributes are preserved.
See also Convert Character Variables to Numeric and Convert All Variables to Opposite Type.
