LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 2001, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 9 Oct 2001 10:24:01 -0700
Reply-To:     "William W. Viergever" <wwvierg@ATTGLOBAL.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "William W. Viergever" <wwvierg@ATTGLOBAL.NET>
Subject:      Re: Excel DDE - Help with INFILE statement
Comments: To: Peter Crawford <peter.crawford@DB.COM>
In-Reply-To:  <>
Content-Type: text/plain; charset="iso-8859-1"; format=flowed

Ditto here: use the colon modifier.

And although, NOTAB is used mainly when you've imbedded blanks in your cell data, I *always* use it when reading/writing from/to Excel - it's never given me any troubles either.

In other words, although Mark Terjeson is technically correct in that, in this case w/ only numerics, you probably don't need it - I don't believe this is the cause of your problem..

Rather, as Peter has pointed out, you are, in effect, reading into the next cell by using COMMA7. when some of your cells are shorter than this.


At 05:47 PM 10/9/2001 +0100, Peter Crawford wrote: >with delimited input _never_ use lengths on unmodified informats in >the input statement ! > >Either remove informats to an attrib or informat statement, or place a >colon(:) modifier before the informat in the input statement. > >What happens is that ......... >sas respects your request to use 7 bytes of the input buffer for your >informat comma7., >before it uses its own judgement about delimiters. >By then delimiters have disappeared ... >read as data by your informat with any value narrower than 7. > >As I interpret what you want to happen, .... >when the informats are "colon modified" or implied, the infile engines >deliver input buffer data for each variable according to the delimiters >before considering informat lengths. > >keep trying >good luck >Peter Crawford > > > >Datum: 09/10/2001 16:30 >An: > > > > >Antwort an: "Krone, Ben --- Manager - Operations Systems --- >GO" <Ben.Krone@AF.COM> > >Betreff: Excel DDE - Help with INFILE statement >Nachrichtentext: > >Need some help on reading some data from Excel into SAS via DDE. > >Here is an example of a row of data as it appears in Excel sheet. > > 9,071 9,333 10,002 8,048 8,392 8,622 8,626 8,470 > 9,465 9,203 9,355 9,178 > >Here is the code I am using to read this data in: > >filename exin dde 'excel|sheet1!r181c5:r181c16'; > >data wrk1; > infile exin notab dlm='09'x missover dsd; > input (a1-a12)(comma7.); >run; > >This is what I get in the log: > >NOTE: The infile EXIN is: > DDE Session, > SESSION=excel|sheet1!r181c5:r181c16,RECFM=V, > LRECL=256 > >NOTE: Invalid data for a4 in line 1 22-28. >NOTE: Invalid data for a5 in line 1 29-35. >NOTE: Invalid data for a6 in line 1 36-42. >NOTE: Invalid data for a7 in line 1 43-49. >NOTE: Invalid data for a11 in line 1 71-77. >NOTE: Invalid data for a12 in line 1 78-84. >RULE: >----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 > > > >RULE: >----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 > >1 CHAR 9,071 . 9,333 . 10,002 . 8,048 . 8,392 . 8,622 . 8,626 . 8,470 >. 9,465 . 9,203 . 9,355 . 9,178 96 > ZONE > 232333202323332023323332023233320232333202323332023233320232333202323332023233320232333202323332 > NUMR > 09C0710909C33309010C0020908C0480908C3920908C6220908C6260908C4700909C4650909C2030909C3550909C1780 >a1=9071 a2=9333 a3=100 a4=. a5=. a6=. a7=. a8=8626 a9=8470 a10=946 a11=. >a12=. _ERROR_=1 _N_=1 >NOTE: 1 record was read from the infile EXIN. > The minimum record length was 96. > The maximum record length was 96. >NOTE: The data set WORK.WRK1 has 1 observations and 12 variables. >NOTE: DATA statement used: > real time 0.07 seconds > cpu time 0.05 seconds > >It appears to be reading the first 2 records fine, then it goes south from >there. > >Any help would be greatly appreciated. > >Thanks >Ben > > >****************************************************************** >This message contains information that is confidential >and proprietary to American Freightways Corporation >or its affiliates. It is intended only for the recipient >named and for the express purpose(s) described therein. >Any other use is prohibited. >******************************************************************* > > > > > >-- > >Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte >Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail >irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und >vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte >Weitergabe dieser Mail ist nicht gestattet. > >This e-mail may contain confidential and/or privileged information. If you >are not the intended recipient (or have received this e-mail in error) >please notify the sender immediately and destroy this e-mail. Any >unauthorized copying, disclosure or distribution of the material in this >e-mail is strictly forbidden.

------------------------------------------------------------ William W. Viergever Voice : (916) 483-8398 Viergever & Associates Fax : (916) 486-1488 Sacramento, CA 95825 E-mail : ------------------------------------------------------------

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