LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (December 2001, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page