ODS CSV Double Quoting Problem

[deleted_user]

Hi, hope someone has seen this before.

I'm outputting a dataset to csv format using the ODS CVS file="C:\test.csv" option.

In the data, I have a fund code column which contains data ($4. format) like AB, G4, 36, FF,2A-1, 2E-1... etc.

The problem is, in the CSV output from Proc Report opened in Notepad, fund code AB,G4,36,FF,2A-1 has double quotes around them however fund code 2E-1 doesn't !!

Its like the ODS is picking which ones to double quote...and what not to quote. The real problem is, when I open the CSV in Excel, 2E-1 is displayed as an exponent (2.00E-01)!!! - and no matter how I format this in Excel, I dont get the actuall value back! All because it has no double quotes.

Is there anyway around this problem?

Thanks.

Message was edited by: drwho Message was edited by: drwho

[Tim_SAS]

Unlike the other codes, 2E-1 is a number in scientific notation, so ODS CSV thinks it's a number and doesn't quote it. If you're using SAS 9.2, you can try the quote_by_type="yes" option.
[pre]
ods csv file="C:\test.csv" options(quote_by_type='yes');
[/pre]
The documentation for the CSV tagset is in the tagset itself. Use this statement to list the CSV quick reference in the log:
[pre]
options(doc='quick')
[/pre]

[Peter_C]

my standard reply, is avoid that destination, for a simple CSV file
Simple data step code delivers the data portion of a csv file.[pre]data _null_ ;
file "&outfile" dsd lrecl=10000 ;
set &data ;
put &columns ;
run ;[/pre] Where:
1 you want a csv file written with a path and filename given in macro variable &outfile
2 you have named your data set in macro variable &data,
3 you have listed the columns (in the order) to be written to the csv file, in macro var &columns

There is little extra if you want column headers
*1 generate header row, creating delimiters "," between column names ;[pre]%let qc_cols = %Qsysfunc( tranwrd( %sysfunc( compbl( &columns )), %str( ), %str(,))) ;[/pre] and use it in the data step like: [pre]data _null_ ;
file "&outfile" dsd lrecl=10000 ;
if _n_ = 1 then put "&qc_cols" ;
set &data ;
put &columns ;
run ;[/pre] The method uses little more code than proc export and works in all environments including zOS/MVS /IBM mainframes.
The example code uses the default delimiter, comma. To use a pipe delimiter, add DLM='|' to the FILE statement, and replace blank with pipe between column headers, like[pre]%let qc_cols = %Qsysfunc( tranwrd( %sysfunc( compbl( &columns )), %str( ), %str(|))) ;[/pre]If a data column contains a delimiter then the method protects the cell by quoting it. If a cell contains a quote mark, then that cell will be protected with quotes. This protection technique is tolerated by all import routines I've come across, (exceptions were some "home-grown" routines).


One limitation of this method is the size of a macro variable, because the complete column header is stored in a macro variable. I hope you would not need a column header wider than 64K (!??!)

[Kwok]

Good Idea. But how would you use the label instead of the variable name as the header ?

Thanks

[Peter_C]

to achieve labels in the way that proc print does labels, is a little more difficult but still do-able. The main problem is that I would want to have a "VAR list" that is entirely independant of the stored data order.
%let data = your_data_whatever ;
%let outfile = /path/to/where/you.want.this/datafile.csv ;
%let columns = your var list this that and the other ;
%let delimiter =',' ; *for simple traditional comma separated values ;
%let delimiter ='09'x ; * tabs as an example demanding quote support ;

%let data = sashelp.class ;
%let outfile = datafile.csv ;
%let columns = name height age ;

*1 get dsdv into required shape;[pre]data v/view=v ;
retain &columns ;
keep &columns ;
set &data ;
stop ;
run ;[/pre]*2 pull column headers ;[pre]proc contents data=v noprint out=_data_;
run ;[/pre]*3 push them into a macro var ;[pre]proc sql noprint ;
select quote( quote( trim( coalescec( label, name ))))
into :labels separated by " &delimiter "
from &syslast
order by varnum
;
%put &sqlobs columns ;
quit ;[/pre]*3 push data to csv ;[pre]data _null_ ;
file "&outfile" dsd lrecl=30000 dlm= &delimiter ;
if _n_ = 1 then put &labels ; * &labels is already quoted ;
set &data ;
put &columns ;
run ;[/pre]*4 just to check in sas;[pre]proc fslist file="&outfile" ;
run ;[/pre]*5 just to check in default app, normally excel ;[pre]dm 'winexecfile "&outfile"' ;[/pre]

The only additional trouble is building that column header row.
I would suggest placing more info into column headers. Add Name as well as Label. Datatype or $length and informat might help re-users of the file, and could easily be constructed in that column header.

hope this adds some value

PeterC

[data_null__]

> The main problem is that I would want to have a "VAR
> list" that is entirely independant of the stored data
> order.

To achieve a function that is even more VAR statement like consider this modification of your nice program. It has the dubious distinction of putting a variable multiple times. Plus PROC TRANSPOSE will fail if a variable listed in &COLUMNS does not exist.

[pre]
%let data = your_data_whatever ;
%let outfile = /path/to/where/you.want.this/datafile.csv ;
%let columns = your var list this that and the other ;
%let delimiter =',' ; *for simple traditional comma separated values ;
%let delimiter ='09'x ; * tabs as an example demanding quote support ;

%let data = sashelp.class ;
%let outfile = datafile.txt;
%let columns = name height age name;

*1 Expand the varlist, may include a
SAS Variable List and/or repeated var names;
proc transpose data=&data(obs=0) out=_data_;
var &columns;
run;
%let varlist = &syslast;

proc sql noprint;
select _name_ into :columns separated by ' ' from &varlist;
quit;
data _null_ ;
file "&outfile" dsd lrecl=30000 dlm= &delimiter ;
if _n_ eq 1 then do;
length _label_ $256 _header_ $256;
call missing(_label_);
* _label_ needs to be declared because it may
not exist in output data from PROC TRANSPOSE;
do until(eof);
set &varlist end=eof;
_header_ = catx(' ',_name_,_label_,Cats('(',vtypeX(_name_),vlengthX(_name_),')'));
put _header_ @;
end;
put;
end;
set &data ;
put &columns;
run;

*4 just to check in sas;
proc fslist file="&outfile" ; run ;
*5 just to check in default app, normally excel ;
dm 'winexecfile "&outfile"'
[/pre] Fixed bug in SQL select statement.


Message was edited by: data _null_;

[Peter_C]

nice variation!
and with underscores around name, it works
;-))
personally, I think proc contents reveals enough with insignificant performance impact while simplifying and eliminating the vtypex) xLengthX() features.... of course my method might run out of macro variable space for exceedingly wide tables....
...
PeterC
looking forward to SGF

[Kwok]

BIG THANKS ! Both methods tested out and both worked beautifully.

One of the interesting statement is the
dm 'winexecfile "&outfile"' in which an excel can be opened within the SAS environment.

One of the nagging thing that annoyed me is after using ODS to output to Excel, the output file is in xml format even though it display as Excel. Columns are squeezed and headdings ,text wrapped.and file size is Large. Everytime I have to go in and unsqueeze the columns, unwrap the headings and save it in xls(to reduce the size). SAS support has told me to use DDE to correct these problems.
I am able to use the DDE to open and save the xlm into xls but still have to be manually adjust the columns width and unwrap the heading.
Will the DM winexecfile... will allow me to set the excel parameters straight automatically ?

Thanks

[Cynthia_sas]

When you use ODS CSV to create a file that Excel can open, you are only creating a CSV file -- so what you are experiencing is the fact that Excel has a preference for its own algorithm for calculating default column widths and deciding whether a cell is character or numeric or how wrapping should occur.

If you used TAGSETS.EXCELXP, instead of ODS CSV then you would get an XML file which DOES have suboptions to allow you to modify or specify column widths, send Microsoft formats to Excel, etc. The "flavor" of XML that's created is Excel 2003 XML. This is a perfectly acceptable format for Excel, as the XML conforms to the Spreadsheet Markup Language specification as set forth by Microsoft.

The XML file is verbose and larger, sometimes than a "native" binary Excel file, that's what I would expect when comparing the sizes of an ASCII text file (CSV or XML) to a BINARY file (.xls).

In your original post you said you were using ODS CSV and in this last post, you implied that you were getting XML, which suggests that sometime between the first post and this post you have switched from ODS CSV to ODS TAGSETS.EXCELXP.

I don't know about setting Excel parameters using DDE, but this paper does describe how to use the extensive sub-option list for TAGSETS.EXCELXP to perform customizations:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf
and you might also find some useful information here:
http://support.sas.com/rnd/papers/2010/under_hood_excelxp.zip
http://support.sas.com/rnd/base/ods/templateFAQ/Excelsamples.html

cynthia