Date: Fri, 26 Jan 2007 15:07:53 -0800
Reply-To: Haris <Karovaldas@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Haris <Karovaldas@GMAIL.COM>
Organization: http://groups.google.com
Subject: Re: SAS Dataset as INFILE
In-Reply-To: <403593359CA56C4CAE1F8F4F00DCFE7D085E9632@MAILBE2.westat.com>
Content-Type: text/plain; charset="iso-8859-1"
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 -
| 
