Convert All Variables to Opposite Type

From sasCommunity

The task is to create a successor to a SAS data set, replacing each character variable in the original with a numeric variable, and each numeric variable in the original with a character variable; this is not necessarily a useful thing to do. Here is a method which preserves variable order and variable attributes. First create a data set for the demonstration:

proc sql;
create table mixedtype 
 (   Month num
   , Year num
   , MeanTemp char(6) Label = 'Mean Temperature (Celsius)'
   , Rainfall char(6)  Label = 'Rainfall (Centimeters)'
 );
insert into mixedtype (Month, Year, MeanTemp, Rainfall)
 values (1, 2008, '5.5', '6.2')
 values (2, 2008, '6.4', '5.9')
 values (3, 2008, '9.8', '4.9')
 ;

Result:

                    Mean
                    Temperature  Rainfall
   Month      Year  (Celsius)    (Centimeters)
----------------------------------------------
       1      2008  5.5          6.2
       2      2008  6.4          5.9
       3      2008  9.8          4.9

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 catx (   ' ' 
                                , 'input ('
                                , name
                                , ', f32. ) as'
                                , name
                                , 'label ='
                                , quote( strip(label) )                               
                              )
        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:

left( put ( Month , best12. ) ) as Month label = ""
left( put ( Year , best12. ) ) as Year label = ""
input ( MeanTemp , f32. ) as MeanTemp label = "Mean Temperature (Celsius)"
input ( Rainfall , f32. ) as Rainfall label = "Rainfall (Centimeters)"

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 switchall as select &selections from mixedtype;
%symdel selections;

Result:

                                   Mean
                            Temperature       Rainfall
Month         Year            (Celsius)  (Centimeters)
------------------------------------------------------
1             2008                  5.5            6.2
2             2008                  6.4            5.9
3             2008                  9.8            4.9

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 ('SWITCHALL','MIXEDTYPE')
 order by varnum, memname desc;
quit;

Result:

                            Column
Column      Member       Number in  Column    Column  Column
Name        Name             Table  Type      Length  Format    Column Label
----------------------------------------------------------------------------------------------
Month       SWITCHALL            1  char          12
Month       MIXEDTYPE            1  num            8
Year        SWITCHALL            2  char          12
Year        MIXEDTYPE            2  num            8
MeanTemp    SWITCHALL            3  num            8            Mean Temperature (Celsius)
MeanTemp    MIXEDTYPE            3  char           6            Mean Temperature (Celsius)
Rainfall    SWITCHALL            4  num            8            Rainfall (Centimeters)
Rainfall    MIXEDTYPE            4  char           6            Rainfall (Centimeters)

This demonstrates that variable order and variable attributes are preserved.

Limitation: This technique will not produce correct results for variables requiring special informats (most notably, times and dates).

See also Convert Numeric Variables to Character and Convert Character Variables to Numeric.