Date:         Fri, 14 Dec 2001 20:30:00 -0800
Reply-To:     Paul Choate <pchoate@JPS.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul Choate <pchoate@JPS.NET>
Subject:      Re: Excel Import
Comments: To: Ed Heaton <edheaton@erols.com>
Comments: cc: Edward Heaton <EdwardHeaton@Westat.com>
Content-Type: text/plain; charset=us-ascii

Thanks!  -  you should sell this to SAS for version 9

Ed Heaton wrote:

> Paul,
>    Here is a clean and simple method if you have the following macro in your
> SAS Autocall library.  (If not, it is not at all simple.)
>
> --------------------------------The macro. -----------------------------
> %macro minimizeTextFields ( data=&sysLast , out= , debugging=0 ) ;
>    %let mPrint = %sysFunc( getOption( mPrint ) ) ;
>    %let notes  = %sysFunc( getOption( notes  ) ) ;
>    %if not &debugging %then %do ;  Options noMPrint noNotes ;  %end ;
>    /* Make a data set that contains a list of the variables in the input
>    data set.  We will use this list to create macro variables to do the
>    work that this macro will perform. */
>    Proc contents
>       data=&data
>       out=&sysMacroName._vars ( keep= name varNum type )
>       %if not &debugging %then noPrint ;
>    ;
>    Run ;
>    Proc sql
>       %if not &debugging %then noPrint ;
>    ;
>       /* Create a macro variable list of the variables in this data set,
>       in their current order. */
>       Select
>          trim( name ) into :&sysMacroName._orderedVars separated by " "
>          from &&sysMacroName._vars
>          order by varNum
>       ;
>       /* Create a macro variable (VARPAIRS) that contains SELECT clauses
>       that will create variable pairs for each character variable in the
>       input data set.  The first variable hold the name of the character
>       variable, and the second variable will hold it's maximum length.
>          Also create a macro variable (STATEMENTS) that contains
>       commands to output two variables; the first will contain the name
>       of the text variable and the next will contain it's length.  There
>       will be a set of these commands for every text variable in the
>       input data set.  This macro variable will then be used in a DATA
>       step to create a data set containing a list of the character
>       variables and the maximum length of the text strings in that
>       column of input data.
>          Finally, create a macro variable (DROPLIST) that will contain a
>       list of all of the variables that initially contained the lengths
>       of the maximum text variables.  This list will be used in the DATA
>       step that follows this SQL procedure. */
>       Select
>             "max(length("||trim(name)||")) as _L"||trim(name)
>           , "name='"||trim(name)||"';maxLen=_L"||trim(name)||";Output"
>           , "_L"||trim(name)
>             into
>                :&sysMacroName._varPairs separated by ","
>              , :&sysMacroName._statements separated by ";"
>              , :&sysMacroName._dropList separated by " "
>          from &&sysMacroName._vars
>          where ( type eq 2 )
>       ;
>       /* Create a table that contains pairs of variables.  The first of
>       the pair is the variable name, the second is the length of the
>       longest character string in that variable. */
>       Create table &sysMacroName._varLengths as
>          select &&&sysMacroName._varPairs from &data
>       ;
>       %if &debugging %then %do ;
>          Select * from &&sysMacroName._varLengths ;
>       %end ;
>    Quit ;
>    /* Now use the STATEMENTS macro variable in a DATA step to put all of
>    the variableName-maximumLength pairs from the VARLENGTHS data set
>    into multiple rows of two columns. */
>    Data &sysMacroName._xposed ;
>       Length name $32 ;
>       Set &&sysMacroName._varLengths ;
>       &&&sysMacroName._statements ;
>       %if not &debugging %then %do ;
>          Drop &&&sysMacroName._dropList ;
>       %end ;
>    Run ;
>    %if &debugging %then %do ;
>       Proc print data=&&sysMacroName._xposed ;  Run ;
>    %end ;
>    /* Use the XPOSED data set to create a macro variable (LENGTHS) that
>    contains all of the variable length assignments. */
>    Proc sql
>       %if not &debugging %then noPrint ;
>    ;
>       Select
>          trim( name ) || " $" || trim( left( put( maxLen , best5. ) ) )
>             into :&sysMacroName._lengths separated by " "
>          from &&sysMacroName._xposed
>       ;
>    Quit ;
>    Proc dataSets library=work noList ;
>       Delete
>          &&sysMacroName._vars
>          &sysMacroName._varLengths
>          &&sysMacroName._xposed
>       ;
>       Run ;
>    Quit ;
>    Data &out ;
>       Retain &&&sysMacroName._orderedVars ;
>       Length &&&sysMacroName._lengths ;
>       Set &data ;
>    Run ;
>    %if not &debugging %then %do ;
>       Options &mPrint &notes ;
>    %end ;
> %mEnd minimizeTextFields ;
> -----------------------------End of macro. -----------------------------
>
> Okay, I'll admit that there is nothing simple about the above macro.  But I
> believe you will find it data driven and unlikely to interfere with anything
> else SAS is doing when it is called.  (Of course, it must not be called in
> any other step.)  If you do have this macro in your AutoCall library, then
> nothing could be simpler as the following code will demonstrate.
>
> Data test ;
>    Length a b c $30 ;
>    Array abc [*] $ a b c ;
>    Do i=1 to 10 ;
>       Do j=1 to 3 ;
>          abc[j] = repeat(
>             byte( 64 + ceil( 26 * ranUni( 43541 ) ) )
>           , ceil( 30 * ranUni( 43541 ) )
>          ) ;
>       End ;
>       Output ;
>    End ;
> Run ;
> Proc contents data=test ;
> Run ;
> %minimizeTextFields( data=test , out=done , debugging=0 )
> Proc contents data=done ;
> Run ;
>
> This macro is useful not only when reading MS Excel data, but also when
> reading MS Access memo fields.  I'm sure it's also useful other places.
>
> Ed Heaton
> 10318 Yearling Drive
> Rockville, MD 20850-3517
> (301) 424-8186
> mailto:edheaton@erols.com
> http:\\users.erols.com\edheaton
>
> Date:         Tue, 11 Dec 2001 20:51:53 -0800[PARA]Reply-To:     Paul Choate
> <pchoate@JPS.NET>[PARA]Sender:       "SAS(r) Discussion"
> <SAS-L@LISTSERV.UGA.EDU>[PARA]From:         Paul Choate
> <pchoate@JPS.NET>[PARA]Subject:      Excel Import[PARA]Content-Type:
> text/plain; charset=us-ascii[PARA][PARA]Hi all -[PARA][PARA]I'm looking for
> a clean&simple way to control variable lengths during a[PARA]data import
> from Excel to SAS.  The default for character variables
> is[PARA]$255.[PARA][PARA]If I port Excel => DBase => SAS the columns come
> out okay (one byte[PARA]wider than I'd like).[PARA][PARA]I'd like something
> like the scantype argument on proc access that not[PARA]only reads data
> type, but also picks up width.[PARA][PARA]Please respond to my work email (I
> can't get the newserver at
> work).[PARA][PARA]pchoate@dds.ca.gov[PARA][PARA]Thanks - Paul