As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Convert Numeric Variables to Character

From sasCommunity
Jump to: navigation, search

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.