BookmarkSubscribeRSS Feed

SAS Tip: Convert Numeric Variables to Character

Started ‎05-10-2019 by
Modified ‎05-10-2019 by
Views 2,725

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.

 

Version history
Last update:
‎05-10-2019 10:58 AM
Updated by:
Contributors

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags