How many of you have been given a SAS data set with variables such as Age, Height, and Weight and some or all of them were stored as character values instead of numeric? Probably EVERYONE! Yes, we all know how to do the old "swap and drop" (rename and convert), but wouldn't it be nice to perform the conversion in one macro call? You can download the Char_to_Num macro (for free) from my author site, from the book Cody's Collection of Popular Programming Tasks, or from the listing right here in the blog. You call the macro with the name of the original SAS data set that contains one or more variables you want to convert, the name of the SAS data set for the converted variables, and a list of character variables that need converting. Right after the macro listing, I'll show you an example:
Here is a listing of the macro:
*Macro to convert selected character variables to numeric variables; %macro char_to_num(In_dsn=, /*Name of the input data set*/ Out_dsn=, /*Name of the output data set*/ Var_list= /*List of character variables that you want to convert from character to numeric, separated by spaces*/); /*Check for null var list */ %if &var_list ne %then %do; /*Count the number of variables in the list */ %let n=%sysfunc(countw(&var_list)); data &Out_dsn; set &In_dsn(rename=( %do i = 1 %to &n; /* break up list into variable names */ %let Var = %scan(&Var_list,&i); /*Rename each variable name to C_ variable name */ &Var = C_&Var %end; )); %do i = 1 %to &n; %let Var = %scan(&Var_list,&i); &Var = input(C_&Var,best12.); %end; drop C_:; run; %end; %mend char_to_num; |
As an example, the code below creates a SAS data set (Contains_Chars) followed by a call to the macro:
data Contains_Chars; input Name $ Age $ Height $ Weight $; datalines; Ron 55 72 180 Jane 57 63 101 ; %Char_to_Num(In_Dsn=Contains_Chars, Out_Dsn=Corrected, Var_List=Age Height Weight) |
The new data set Corrected has the same variable names as the character variables in the Contains_Chars data set except they are now all numeric variables. Here is a section from PROC CONTENTS:
I hope this macro will save you some time on your next project.
3 Comments
We always assume that everyone employs macros to work with SAS datasets. Is it possible to view the task solution without using macros? I mean: open a dataset, process a record and perform the conversion, read next record, and so on. I know that macro users will say "yuck! what a waste of time." But I think it is better to learn to walk before you run. Thanks.
Happy new year Ron.
I guess this macro will fail if input variables are comma or dollars formatted.
Yes, it might be good to add another parameter to pass in the desired format(s) to use.