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 (December 2002, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 11 Dec 2002 21:32:56 +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: SAS au Fromage
Comments: To: sashole@bellsouth.net
Content-type: text/plain; charset=iso-8859-1

Hi Paul I think when you ask this "but does it really separate ", you may have missed one efffect of the file statement DSD option. >That inserts the comma delimiters OK, not between the "constants" that might make a header

But the original code is almost fool-proof (subject to there being appropriate default formats)

It will be "fool-proof" when a bug in file-DSD logic is fixed... )not only mask (quote() ) a value containing a delimiter, )they also have to mask a cell containing " (quote mark) (That bug causes too much trouble when a variable starts ( with an unbalanced quote(")

Here is some test data data stuff; retain charbit 'qwertzuiopü' datetim "%now"dt; format datetim datetime. ; do num1 = 1e21/81 to 1e15 by -1e18; integ = int( num1/1e16 ); substr( charbit, length(charbit)) = ' ';*trailing spaces; output; end; stop; run;

To make the result foolproof for now, add quotes to all columns - possibly making the output much wider. Or possibly apply to the character columns only. data _null_; file 'c:\brie.csv' dsd lrecl=32000; set stuff ; put (_all_) (~); * quote everything; run; Amazingly that tilde(~) modifier quotes numerics, even dates as well, but the DSD infile option deals with that too! As it will on this "brie" data proc fslist file = 'c:\brie.csv' ; run; +FSLIST: c:\brie.csv-----------------------------------------+ | ----|----10---|----20---|----30---|----40---|----50---| |00001 "qwertzuiop","11DEC02:21:17:56","1.2345679E19","1234" | |00002 "qwertzuio","11DEC02:21:17:56","1.1345679E19","1134" | |00003 "qwertzui","11DEC02:21:17:56","1.0345679E19","1034" | |00004 "qwertzu","11DEC02:21:17:56","9.345679E18","934" | |00005 "qwertz","11DEC02:21:17:56","8.345679E18","834" | |00006 "qwert","11DEC02:21:17:56","7.345679E18","734" | |00007 "qwer","11DEC02:21:17:56","6.345679E18","634" | |00008 "qwe","11DEC02:21:17:56","5.345679E18","534" | |00009 "qw","11DEC02:21:17:56","4.345679E18","434" | |00010 "q","11DEC02:21:17:56","3.345679E18","334" | |00011 ,"11DEC02:21:17:56","2.345679E18","234" | |00012 ,"11DEC02:21:17:56","1.345679E18","134" | |00013 ,"11DEC02:21:17:56","3.4567901E17","34" | |00014 *** END OF FILE *** | +------------------------------------------------------------+

Or one can quote just _character_ columns, while at the same time providing best32. numeric values when applicable, and if date or datetime format is appropriate data _null_; file 'c:\brie2.csv' dsd lrecl=32000; set feta; *** write all variables in data set feta; put (_all_) (:); format _character_ $quote32000. /* fully quote char vars */ _numeric best32. /* max precision numerics, with specific overrides as necessary */ &dates yymmdd10. &datetimes datetime23.3 ; run; Of course those &dates and &datetimes varlists must be populated from some source..........

Also convenient, is that _none_ of these wide formats result in wide output unless of course, the data is wide! +FSLIST: c:\brie2.csv------------------------------------------------+ | ----|----10---|----20---|----30---|----40---|----50---|----60-| |00001 "qwertzuiop",11DEC2002:21:17:55.980,12345679012345679872,1234 | |00002 "qwertzuio",11DEC2002:21:17:55.980,11345679012345679872,1134 | |00003 "qwertzui",11DEC2002:21:17:55.980,10345679012345679872,1034 | |00004 "qwertzu",11DEC2002:21:17:55.980,9345679012345679872,934 | |00005 "qwertz",11DEC2002:21:17:55.980,8345679012345679872,834 | |00006 "qwert",11DEC2002:21:17:55.980,7345679012345679872,734 | |00007 "qwer",11DEC2002:21:17:55.980,6345679012345679872,634 | |00008 "qwe",11DEC2002:21:17:55.980,5345679012345679872,534 | |00009 "qw",11DEC2002:21:17:55.980,4345679012345679872,434 | |00010 "q",11DEC2002:21:17:55.980,3345679012345679872,334 | |00011 "",11DEC2002:21:17:55.980,2345679012345679872,234 | |00012 "",11DEC2002:21:17:55.980,1345679012345679872,134 | |00013 "",11DEC2002:21:17:55.980,345679012345679872,34 | |00014 *** END OF FILE *** | |00015 | +--------------------------------------------------------------------+

Note that $quote. is also quoting the empty character string on obs 11-13 unlike the ~ modifier

Datum: 11/12/2002 19:34 An: SAS-L@LISTSERV.UGA.EDU

Antwort an: sashole@bellsouth.net

Betreff: Re: SAS au Fromage Nachrichtentext:

Mike,

Sure it is quick, but does it really separate the printed values with commas? Plus, there is a potential caveat - SAS will print numerics using the best12. format, so in the case shown above, ACCTNO would be printed as 1.2345679E15, i.e. result in unaffordable lost of precision. Perhaps this is a little more robust and comma-separated:

146 data _null_ ; 147 retain num1 123456 char1 'char1' 148 acctno 1234567890123456 char2 'char22' 149 ; 150 put (_all_) (: 32. +(-1) ',') ; 151 run ;

123456,char1,1234567890123456,char22

In the case the numbers are not integer, the format has to be changed accordingly, for example,

156 data _null_ ; 157 retain num1 123.456 char1 'char1' 158 acctno 1234567890123456 char2 'char22' 159 ; 160 put (_all_) (: 32.3 +(-1) ',') ; 161 run ;

123.456,char1,1234567890123456.000,char22

the side effect being the trailing zeroes when printing integers.

Kind regards, ------------------ Paul M. Dorfman Jacksonville, FL ------------------

----Original Message Follows---- From: "Michael S. Zdeb" <msz03@HEALTH.STATE.NY.US>

Hi...quick way to create a comma-separated value file from a SAS data set...

data _null_; file 'c:\brie.csv' dsd; set feta; *** write only variables in data set feta; put (_all_) (:); run;

Mike Zdeb New York State Department of Health ESP Tower - Room 1811 Albany, NY 12237 P/518-473-2855 F/630-604-1475

_________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail

--

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