Haris,

Sorry, I'm continually forgetting to strip leading and trailing blanks when quoting a value from a character variable. Consider the following.

Data foo ; Attrib a length=$1 label='This label has "double" quotes' b length=$1 label="This label has 'singe' quotes" c length=$1 label="This label 'as an apostrophe" d length=$1 label='This label has no quotes' ; Call missing( of a--d ) ; Run ; Proc contents data=foo out=vars( keep= name label ) noPrint ; Run ; Data _null_ ; Set vars ; wontWork = "'" || label || "'" ; qLabel = quote( strip(label) ) ; Put label= / wontWork= / qLabel= / ; Run ;

So, the "fixed" macro is

/*********************************************************************** 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.) libRef= allows the user to specify a libRef for the library where this macro will write it's temporary datasets. This library will be in a subdirectory named CHAR2NUM under the directory that SAS is using for the WORK library. (The default is ________ in hopes that it is not used by the caller of this macro.) 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. , libRef=________ , debugging=0 ) ;

%let data = &data ; /* This assignment is critical. */ %local path allVars labelers renamers drops assignments ;

/* Find the Windows directory for the WORK library and create a subdirectory under that with the name of this macro. Then create a libRef to that subdirectory. This Windows directory will be deleted when SAS automatically cleans up the WORK directory. */ Data _null_ ; Call symPut( "path" , dCreate( "&sysJobId" , "%sysFunc( pathName(work) )" ) ) ; Run ; LibName &libRef "&path" ;

/* 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=&libRef..AllVars( keep= name varNum ) noPrint ; Run ;

/* Create a dataset containing the names of all of the desired character variables. */ Proc contents data=&data( keep=&charVars ) out=&libRef..CharVars( keep= name type label ) noPrint ; Run ;

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 &libRef..AllVars order by varNum ; Drop table &libRef..AllVars ; /* 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 &libRef..CharVars 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 &libRef..CharVars where ( type eq 2 ) ; Drop table &libRef..CharVars ; Quit ;

/* In case any variables were specified that are already numeric, issue a warning message to the log. */ Data _null_ ; Set &libRef..CharVars( where=( type ne 2 ) ) ; Put "WARNING:" +1 name "is not a character variable." ; Run ; Proc sql noPrint ; Drop table &libRef..CharVars ; Quit ; LibName &libRef clear ;

/* Now, convert the data. */ Data &out( drop=&drops ) ; Retain &allVars ; Label &labelers ; Set &data( rename=(&renamers) ) ; &assignments Run ;

%mEnd char2num ; /*====================================================================*/

Ed

Ed Heaton RW-4541 #4818

-----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Haris Sent: Friday, January 26, 2007 6:08 PM To: sas-l@uga.edu Subject: Re: SAS Dataset as INFILE

Ed,

Thanks so much for sharing your macro. I have a peculiara problem:

Select catS( name , "=" , quote(label) )

This line returns an error message. I replaced it with:

Select catS( name , "='" , label, "'" )

and the macro just fine. Room for improvements: add the ability to change multiple formats at the same time (char to num as well as char to date); add format= information in addition to informat=.

Thanks again!

On Jan 26, 12:32 pm, EdHea...@WESTAT.COM (Ed Heaton) wrote: > Sorry about the word-wrapping in my last post. I tend to code in > 80-byte lines and email gets parsed into 72-byte lines. Here is the > cleaned-up macro. > > Haris; > > Of course there are several problems. You can correct for them on a > case-by-case basis. > > I have a macro that I use to handle some of the problems. I just added > code to it to deal with the variable label issue. It already deals with > the variable order issue. > > /*********************************************************************** > 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:EdHea...@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.) > libRef= allows the user to specify a libRef for the library > where this macro will write it's temporary datasets. > This library will be in a subdirectory named CHAR2NUM > under the directory that SAS is using for the WORK > library. (The default is ________ in hopes > that it is not used by the caller of this macro.) > 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. > , libRef=________ > , debugging=0 > ) ; > > %let data = &data ; /* This assignment is critical. */ > %local path allVars labelers renamers drops assignments ; > > /* Find the Windows directory for the WORK library and create a > subdirectory under that with the name of this macro. Then create a > libRef to that subdirectory. This Windows directory will be deleted > when SAS automatically cleans up the WORK directory. */ > Data _null_ ; > Call symPut( > "path" > , dCreate( "&sysJobId" , "%sysFunc( pathName(work) )" ) > ) ; > Run ; > LibName &libRef "&path" ; > > /* 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=&libRef..AllVars( keep= name varNum ) > noPrint > ; > Run ; > > /* Create a dataset containing the names of all of the desired > character variables. */ > Proc contents > data=&data( keep=&charVars ) > out=&libRef..CharVars( keep= name type label ) > noPrint > ; > Run ; > > 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 &libRef..AllVars > order by varNum > ; > Drop table &libRef..AllVars ; > /* 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(label) ) into :labelers > from &libRef..CharVars > 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 &libRef..CharVars > where ( type eq 2 ) > ; > Drop table &libRef..CharVars ; > Quit ; > > /* In case any variables were specified that are already numeric, issue > a warning message to the log. */ > Data _null_ ; > Set &libRef..CharVars( where=( type ne 2 ) ) ; > Put "WARNING:" +1 name "is not a character variable." ; > Run ; > Proc sql noPrint ; Drop table &libRef..CharVars ; Quit ; > LibName &libRef clear ; > > /* 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), > 1600 Research Boulevard, RW-4541, Rockville, MD 20850-3195 > Voice: (301) 610-4818 Fax: (301) 294-3879 > mailto:EdHea...@Westat.com http://www.Westat.com > > > > -----Original Message----- > From: owner-sa...@listserv.uga.edu [mailto:owner-sa...@listserv.uga.edu] > > On Behalf Of Haris > Sent: Friday, January 26, 2007 11:42 AM > To: s...@uga.edu > Subject: Re: SAS Dataset as INFILE > > There are several problems with rename-recompute-drop approach: > > 1. Variable order in the file gets changed > 2. Variable labels get dropped > 3. With many variables and many files there's a lot of work for what it > seems to be a relatively simple issue > > I extended the approach proposed by Gerhard above and developed the > following syntax for label reassignment: > > filename LABELS Temp; > Data _NULL_; > Set COLUMNORDER End = LAST; > If _N_ = 1 Then Put "label "; > Put NAME "= '" LABEL"'" ; > If LAST Then Put ";"; > Run; > data want ; set want ; > %include LABELS ; run ; > FileName _ALL_ Clear; > > The DATA step produces the correct output on my screen; however, in > response to "%include LABELS" SAS gives me the following error message: > > WARNING: Physical file does not exist, C:\...\Temp\SAS Temporary > Files\_TD3556\#LN00023. > ERROR: Cannot open %INCLUDE file LABELS. > > What am I doing wrong? > > On Jan 26, 9:51 am, "Haris" <Karoval...@gmail.com> wrote: > > Hi Ed, > > > Your datasets and parsing look very interesting, but I have no idea > how > > they can help me change the type of several variables from character > to > > numeric. Can you be so kind as to explain a bit more. > > > THanks. > > > On Jan 26, 7:14 am, EdHea...@WESTAT.COM (Ed Heaton) wrote: > > > > Haris; > > > > Methinks you want to use the input buffer (_INFILE_). Of course you > > > can. Of course you have to set it up with an INFILE statement, but > that > > > INFILE statement doesn't need to point to your SAS dataset. > > > > Consider the following. It might spark an idea. > > > > /* Set up a test dataset. */ > > > Data foo ; > > > Input String $char80. ; > > > Put String= ; > > > Cards4 ; > > > This is a test of sorts. > > > Why not a test of programs? > > > Well, a test of code is harder to develop. > > > ;;;; > > > /* Now, use the INPUT statement to parse the text variable. */ > > > Data _null_ ; > > > Set foo ; > > > InFile cards truncOver ; > > > /* Set up the input buffer. */ > > > If ( _n_ eq 1 ) then input @@ ; > > > _inFile_ = String ; > > > Input @1 @'test of' TestType $ @@ ; > > > Put TestType= ; > > > Cards4 ; > > > foo > > > ;;;; > > > > One note: Don't omit the trailing @@ at the end of each INPUT > statement. > > > If you do, you will try to read past 'foo' and the DATA step will > end. > > > > Ed > > > > Edward Heaton, Senior Systems Analyst, > > > Westat (An Employee-Owned Research Corporation), > > > 1600 Research Boulevard, RW-4541, Rockville, MD 20850-3195 > > > Voice: (301) 610-4818 Fax: (301) 294-3879 > > > mailto:EdHea...@Westat.com http://www.Westat.com > > > > -----Original Message----- > > > From: owner-sa...@listserv.uga.edu > [mailto:owner-sa...@listserv.uga.edu] > > > > On Behalf Of Haris > > > Sent: Wednesday, January 24, 2007 5:27 PM > > > To: s...@uga.edu > > > Subject: SAS Dataset as INFILE > > > > Can a SAS format data file be used in the INFILE command... > > read more >- Hide quoted text -- Show quoted text -