On Sat, 9 Feb 2008 23:29:13 -0500, Nat Wooding <Nathaniel.Wooding@DOM.COM> wrote:

>Art > >This is definitely a keeper and I wish that I had thought of it.

A "keeper" is an item which merits visibility and ongoing findability, right? Then I think the place for a keeper is the the SASopedia (http://www.sascommunity.org/wiki/Sasopedia).

> >One further aspect of using the libname engine, both with Excel and also >Access files, when you issue the libname statement, you can now look at the >Excel or Access files using the SAS Explorer. You look at the files and you >can also look at the properties and see what SAS is using as a variable >name and also variable labels. The later can be very helpful when there are >subtle differences between the two and code referencing a variable just >doesn't seem to be recognized (of course, looking at the log should >identify variables that are referenced but which do not exist). > >Nat > >Nat Wooding >Environmental Specialist III >Dominion, Environmental Biology >4111 Castlewood Rd >Richmond, VA 23234 >Phone:804-271-5313, Fax: 804-271-2977 > > > > Arthur Tabachneck > <art297@NETSCAPE. > NET> To > Sent by: "SAS(r) SAS-L@LISTSERV.UGA.EDU > Discussion" cc > <SAS-L@LISTSERV.U > GA.EDU> Subject > Re: OT: Chance to Make SAS-L > History: Did You Know That... > 02/09/2008 06:42 > PM > > > Please respond to > Arthur Tabachneck > <art297@NETSCAPE. > NET> > > > > > > >Did you know that using the excel libname one can easily get around how >SAS reads mixed characters? I may be the only one that didn't but, >frankly, before today, I simply did not know that. > >I, like many of us, often have to read Excel files that are always in >exactly the same format, including one or more fields that really should >only be numbers but, in fact, typically contain non-numeric entries. > >While Paul Choate has always been a big proponent of using the Excel >libname engine I, personally, have always stayed with proc import. > >In response to a recent poster's problem, namely trying to force a mixed >data type field to only read numbers, I discovered a fairly easy way to >implement a reusable solution. In the past, I would open the new Excel >file, add a dummy row that reflected how the rest of the data should look, >set GUESSINGROWS to 1, imported the file and then deleted the first record. > >My suggestion was to open the initial Excel file, copy the first two lines >(i.e., header and first data row), paste them into a blank Excel workbook >and save the new file as a template to use and reuse with each subsequent >import using the Excel libname. My solution, specifically, was: > >libname template 'c:\template.xls'; >libname newdata 'c:\newdata.xls'; >data want; > set template.'sheet1$'n newdata.'sheet1$'n; > if _n_ eq 1 then delete; >run; > >Art > > > >----------------------------------------- >CONFIDENTIALITY NOTICE: This electronic message contains >information which may be legally confidential and/or privileged and >does not in any case represent a firm ENERGY COMMODITY bid or offer >relating thereto which binds the sender without an additional >express written confirmation to that effect. The information is >intended solely for the individual or entity named above and access >by anyone else is unauthorized. If you are not the intended >recipient, any disclosure, copying, distribution, or use of the >contents of this information is prohibited and may be unlawful. If >you have received this electronic transmission in error, please >reply immediately to the sender that you have received the message >in error, and delete it. Thank you.