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
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.
| 
