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.
