Date: Fri, 26 Jan 2007 13:32:43 -0500
Reply-To: Ed Heaton <EdHeaton@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ed Heaton <EdHeaton@WESTAT.COM>
Subject: Re: SAS Dataset as INFILE
In-Reply-To: <1169829716.262044.255990@k78g2000cwa.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"
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: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(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: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 Haris
Sent: Friday, January 26, 2007 11:42 AM
To: sas-l@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 of the DATA
> > step? I tried unsuccessfully, but there may be some ways to do it.
>
> > I inherited a whole bunch of SAS datafiles that have everying stored
as
> > text. I've been looking for ways to reformat a whole bunch of them
and
> > the only way I seem to find is to compute new variables. Variable
> > order is important to me and, after I recompute, it gets changed
> > unless I recompute text variables also. From what I understand, the
> > easiest solution would be to re-read SAS data files anew with proper
> > informats.
>
> > I was about to export all of these datasets as tab-delmited files,
and
> > it occurred to me that I may be able to specify a SAS dataset as an
> > external file without this intermediate step.
>
> > Thanks,
> > Haris- Hide quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -
