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 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 2 Oct 2001 12:21:05 +0100
Reply-To:     Peter Crawford <peter.crawford@DB.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Peter Crawford <peter.crawford@DB.COM>
Subject:      Re: Antwort: RE: Reading a CSV File
Comments: To: "Wang, Zhaoping (USPC Marketing & Investments Division)"
          <ZWang@NA2.US.ML.com>
Content-type: text/plain; charset=iso-8859-1

Hi Zhaoping I remember.

Without seeing the data, I can only guess at why this the problem appears now, for the first time. Perhaps there was some byte following the last '0A'x. I don't think it could be a '0D'x (following the last '0A'x) without disturbing the reading of all rows. You might avoid this and any other empty observations, by testing that some "key feature" value is non-empty before output.. I get this kind of problem when (dde) reading from a spreadsheet range with a row which is "empty" but "present" You can use the sas display manager to examine the file with command fslist 'your csv file' ; hex; bottom You should see the character and hex representation of the last few lines as seen by FSLIST - I assume this should be interpreted the same as an infile statement would interpret the data. It might help clarify things.

Others on sas-l may know better the cause of such a problem.

good luck Peter Crawford

Datum: 01/10/2001 19:26 An: Peter Crawford/Zentrale/DeuBaExt@Zentrale

Betreff: RE: Antwort: RE: Reading a CSV File Nachrichtentext:

Hi, Peter. Hope you still remember me.

The dim='2C0D' has been working very nicely. In a recent CSV file reading, however, I got another subtle problem--the SAS dataset read from the CSV file had one more observation than in the CSV file. It was the last observation, and all variables exhibited missing status.

Why didn't the system stop at the end of the original file, or how was this shadowy observation created ?

Thanks.

Zhaoping

-----Original Message----- From: Peter Crawford [mailto:peter.crawford@db.com] Sent: Tuesday, August 28, 2001 12:44 PM To: Wang, Zhaoping (USPC Marketing & Investments Division) Subject: Antwort: RE: Reading a CSV File

dsd is still necessary, how else is ,, to imply an empty cell ? DSD also interprets "lastname, firstname" as one character cell having value lastname, firstname i.e. it allows data cells to contain a delimiter character as long as the cell is quoted with double quote marks(")

The explicit dlm='2c0d'x overrides the implied dlm=',' that comes with option DSD. Don't consider it a trick - it is only using standard facilities. With dlm='2c0d'x this method considers '0d'x and '2c'x to be delimiters anywhere they occur, unless masked by quote marks(")

DLM='0C0D'x defines two alternative delimiters

This ensures that DSD DLM='09'x accepts commas in columns as data, not delimiters

for further explanation read up the on-line doc and (if yu can find it) SAS technical report P222 Changes & Enhancements to the SAS System v6.07

good luck

(you know how the earn an obligation ! )

Datum: 28/08/2001 15:59 An: Peter Crawford/Zentrale/DeuBaExt@Zentrale

Betreff: RE: Reading a CSV File Nachrichtentext:

The only question I now have is this: Does this trick "replace" DSD (comma delimiter as default) OR they are alternatives so that DSD takes care of comma and the trick takes care of "comma plus unprintable" at the end of a line ?

I think you're at least one of the very few best programmers in the world.

Thanks again and regards.

Zhaoping

-----Original Message----- From: Peter Crawford [mailto:peter.crawford@db.com] Sent: Tuesday, August 28, 2001 11:12 AM To: Wang, Zhaoping (USPC Marketing & Investments Division) Cc: sas-l@listserv.uga.edu Subject: RE: Reading a CSV File

here is my reasoning

As you are receiving "comma separated values" that is, .Csv data, then probably you already use infile option DSD, to support consecutive comma and to avoid using spaces as delimiters. Add infile option DLM='2c0d'x. As in.. Infile <your data file> DSD DLM='2c0d'x ...any.other.options... ; This replaces the default delimiter of comma, with comma ('2c'x) *and* '0D'x = a carriage return character which is treated as data on UNIX, but as part of the "end-of-record" definition on DOS and windows platforms. Having more than one delimiter character is perfectly legal. It doesn't require both. They are alternatives. Finding a "delimiter" at the end of the line will add no problems to the infile processing. This method avoids having an extra pass through your data to correct for this extra character.

Does that explain how 2C and 0D work together ? This log snip shows how I determine the hex value of the character (,)comma 1 %put %sysfunc( putc( %str(,,,), $hex6 )); 2C2C2C

That needed the %str() around the comma characters, to avoid the ",,," being used as parameter separators

Regards Peter Crawford

Datum: 28/08/2001 14:40 An: Peter Crawford/Zentrale/DeuBaExt@Zentrale Kopie: "'Aldi Kraja'" <aldi@wubios.wustl.edu> "'Ian Whitlock'" <WHITLOI1@WESTAT.com>

Betreff: RE: Antwort: Reading a CSV File Nachrichtentext:

Wonderful ! It takes care not only of '2C'x (comma) but also '0D' (the unprintable character).

I also received responses from Aldi Kraja and Ian Whitlock. Aldi offered a Unix script solution which takes out the unprintable character at the end of each line in the file. Ian provided an enlightening explanation so I can understand what hex is and how it works. But you gave me a magic bullet.

But I'm still wondering how and why '2C0D'x is able to take care both '2C'x and '0D'x ?

Thank you all for your help !

Zhaoping

-----Original Message----- From: Peter Crawford [mailto:peter.crawford@db.com] Sent: Tuesday, August 28, 2001 7:43 AM To: Wang, Zhaoping (USPC Marketing & Investments Division) Subject: Antwort: Reading a CSV File

did you get the easy solution to this ? INFILE <your file> DSD DLM=''2C0D'x ...any.other.options...; Treating that '0D'x as a delimiter effectively ignores it ! Regards Peter Crawford

Datum: 24/08/2001 20:57 An: SAS-L@LISTSERV.UGA.EDU

Antwort an: "Wang, Zhaoping (USPC Marketing & Investments Division)" <ZWang@NA2.US.ML.COM>

Betreff: Reading a CSV File Nachrichtentext:

This is surely not the first time I've ever read a CSV file, but the problem seems strange.

The original CSV file looks like this:

RUTH H. WATKINS,0,0

I tried to input 3 variables: name ($ 30), fee (numeric), and discount (numeric).

Here's the error message in the log file:

Invalid data for discount in line 1 .......

------|------|-------|-------|------- (ruler)

RUTH H. WATKINS,0,0. (this period cannot be "seen" in the CSV file)

ZONE 2545444523230 NUMR 0714B9E3C0C0D

name=RUTH H. WATKINS fee=0 discount=. _error_=1 _n_=1

Why can the first 0 be read but the second cannot ? What does "ZONE" and "NUMR" mean ?

Thanks for your help.

Zhaoping

--

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.

--

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.

--

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.

--

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.


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