procsql;
createtable mixedtype as
select*
fromsashelp.class;
quit;
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:
procsql;
selectcase type
when'num' thencatx ( ' '
, 'left( put ('
, name
, ','
, case
whenformat is null then 'best12.'
elseformat
end
, ') ) as'
, name
, 'label ='
, quote( strip(label) )
)
when'char' then name
else catx ( ' '
, quote('Error on type')
, 'as'
, name
)
end
into: selections separated by ' , '
fromdictionary.columns
wherelibname='WORK' andmemname='MIXEDTYPE';
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:
procsql;
createtable allchar asselect &selections frommixedtype;
quit;
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.
fromdictionary.columns
wherelibname='WORK' andmemname in ('ALLCHAR','MIXEDTYPE')
orderby 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
