Date: Thu, 11 Oct 2007 17:06:56 -0400
Reply-To: Ed Heaton <EdHeaton@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ed Heaton <EdHeaton@WESTAT.COM>
Subject: Re: char-to-num conversion problem again
In-Reply-To: <fds55c$ocd$1@news.Stanford.EDU>
Content-Type: text/plain; charset="us-ascii"
Toby,
If all you want is a solution, try the following macro. Save it as
char2num.sas and put it in your autocall library for future use.
/***********************************************************************
MACRO: char2num
OBJECTIVE:
This macro will convert a specified list of character variables and
their data to numeric. The major advantages of this macro are
1. it preserves the order of the variables,
2. it allows the user to use standard SAS variable-list
notation, and
3. it preserves variable labels.
This macro was tested under SAS 9.1.3. It will not run on SAS 8.2
or earlier versions.
VALID: between program steps
USAGE:
%char2num(
data=
, out=
, charVars=
, inFormat=
)
PROGRAMMER:
Edward Heaton, SAS Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1650 Research Boulevard, RW-4541, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-3879
mailto:EdHeaton@Westat.com http://www.Westat.com
PARAMETERS:
data= specifies the input dataset. (The default is the last
data set that was created in this SAS session.)
out= specifies the output dataset. (The default is _data_
which will produce a dataset named DATA1 or DATA2 or
...)
charVars= specifies the character variables that have data that
represent numbers. You can use any of the standard
methods of listing these variables. (The default is
_CHARACTER_ which will include all of the character
variables.)
inFormat= specifies the inFormat that you want to use for the
conversion. (The default is the BEST12. informat.)
debugging= is set to 1 to evoke debugging options. (The default is
0.)
STORAGE: Specify the fully-qualified file name for this macro.
AUDIT TRAIL:
20060127 EH Developed macro to convert character variables to
numeric while preserving the variable order.
20070126 EH Added code to preserve the variable labels.
***********************************************************************/
%macro char2num(
data=&sysLast
, out=_data_
, charVars=_character_
, inFormat=best16.
, debugging=0
) ;
%let data = &data ; /* This assignment is critical. */
%local
path allVars allVarsDataset charVarsDataset labelers renamers
drops assignments
;
/* Create a dataset containing the names of all of the variables in the
input dataset. This will be used to create an ordered list of the
macro variables, so we also need the variable number. */
Proc contents
data=&data
out=_data_( keep= name varNum )
noPrint
;
Run ;
%let allVarsDataset = &sysLast ;
/* Create a dataset containing the names of all of the desired
character variables. */
Proc contents
data=&data( keep=&charVars )
out=_data_( keep= name type label )
noPrint
;
Run ;
%let charVarsDataset = &sysLast ;
Proc sql noPrint ;
/* Create an ordered list of all of the variable names in the input
dataset. Put the list in a macro variable so that it can be
used in a RETAIN statement below. */
Select name
into :allVars separated by ' '
from &allVarsDataset
order by varNum
;
Drop table &allVarsDataset ;
/* Create a list of variable-label assignments that can be used in
a LABEL statement for each of the character variables that have
a variable label. */
Select catS( name , '=' , quote( strip(label) ) ) into :labelers
from &charVarsDataset
where label is not missing
;
Select
/* Create a list of rename clauses that will rename the
variables in the CHARVARS= list by preceding the name
with an underscore. This list will be used in a RENAME=
dataset option. */
catS( name , '=_' , name )
/* Create a list of the variable names from the CHARVARS=
list where each variable name is preceded by an
underscore. This list will be used in a DROP= dataset
option. */
, catS( '_' , name )
/* Create a list of assignment statements that convert the
character value from the input dataset to a numeric
value for the output dataset. */
, catS( name , '=input(_' , name , ",&inFormat);" )
into
:renamers separated by ' '
, :drops separated by ' '
, :assignments separated by ' '
from &charVarsDataset
where ( type eq 2 )
;
Quit ;
/* In case any variables were specified that are already numeric, issue
a warning message to the log. */
Data _null_ ;
Set &charVarsDataset( where=( type ne 2 ) ) ;
Put "WARNING:" +1 name "is not a character variable." ;
Run ;
Proc sql noPrint ; Drop table &charVarsDataset ; Quit ;
/* Now, convert the data. */
Data &out( drop=&drops ) ;
Retain &allVars ;
Label &labelers ;
Set &data( rename=(&renamers) ) ;
&assignments
Run ;
%mEnd char2num ;
/*====================================================================*/
Ed
Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1650 Research Boulevard, RW-4541, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-3879
mailto:EdHeaton@Westat.com http://www.Westat.com
-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of toby989@hotpop.com
Sent: Monday, October 01, 2007 9:03 PM
To: sas-l@uga.edu
Subject: char-to-num conversion problem again
Hi
I am looking for a SIMPLE solution unlike the monster there
http://listserv.uga.edu/cgi-bin/wa?A2=ind0105e&L=sas-l&D=0&P=17317
My apporoach is like:
data psdata.ps2002a;
set psdata.ps2002;
array q _all_;
do over q;
q = input(q,1.0);
end;
run;
It does not work completely. If there is no other way, I would be ok
with
creating a set of new variables that are numeric and then dropping the
old ones.
Any hints?
Thanks Toby
