Date:         Tue, 20 Apr 2004 23:23:28 -0400
Reply-To:     Don Stanley <don_stanley@PARADISE.NET.NZ>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Don Stanley <don_stanley@PARADISE.NET.NZ>
Subject:      Tip: Writing CSV Files

Over time I have come with two macros to create CSV files from SAS
datasets. Today I had some spare time so I started looking into the
experimental (at release 8.2) ODS CSV destination. This is available on all
platforms and is production at 9.1.

This really is useful. using this you can apply all features of SAS,
including formats and where clauses. A simple set of commands such as

ods csv file='c:\mycsvfile.csv' type=csvall ;

proc print data=sashelp.class;
run;

ods csv close;

will happily create a CSV file that reads into EXCEL. However there are
some obvious problems that arise.

(1) the CSV file contains titles and footnotes which isn't always useful
(2) if there are no footnotes or titles, there are 2 empty lines left at
the top of the CSV file
(3) the traditional thing that CSV generators get wrong (or if you wish,
one aspect of CSV files that EXCEL always reads incorrectly). If you have a
character variable that is inherently numeric and contains leading zeroes,
then the leading zeroes are lost when EXCEL reads the CSV file (eg we have
bank account numbers like 00000000756477438 which are text throughout every
system in the bank). To not lose these, the CSV file must contain "="
before the quoted string containing the variable value.

The generated CSV file quotes every field, numeric or character. Quoting is
not enough to get around problem 3 above.

Problem one is easily got round. Switch off the titles and footnotes.

Problem 2 and 3 are resolved in a SAS FAQ. That FAQ is at
http://support.sas.com/rnd/base/topics/templateFAQ/Excel1.pdf and is
typical of the very good ODS faqs that exist.

Given the simplicity of the code once you copy the tagsets to work around
the problems above, other approaches to writing CSV files become near
redundant. The FAQ has lots more info as well covering ODS MARKUP and ODS
XML for writing files to load to EXCEL.

Sample code to illustrate and work around the problems above to create CSV
files, is

proc template;
define tagset tagsets.newcsv;
parent = tagsets.csv;
notes "This is the CSV definition";
/* we removed the start: put NL. It was putting a line at the beginning of
the table. */
define event table;
finish:
put NL;
end;
/* We added finish: */
/* This makes it so that the line is put at the finish of the even row
instead of every event row. */
define event row;
finish:
put NL;
end;
/*
 add = in front of cells to get correct read by EXCEL on char fields with
leading
 zeroes
 */
 define event data;
    put "," / if !cmp( COLSTART , "1" );
    put '='"""" / if cmp( TYPE , "string" );
    put VALUE;
    put """" / if cmp( TYPE , "string" );
 end;
end;
run;

ods tagsets.newcsv body='c:\test.csv' ;
data temp ;
  set sashelp.class;
  myfield = put(_n_,z5.) ; /* generate a field that must have an = in front
in */
                           /* the CSV file */
run ;

proc print data=temp ;
run;
ods tagsets.newcsv close;


Useful stuff, and for what I'm working on, one of the nicest bits of ODS.
We quite literally use CSV or XML daily. And another excellent FAQ from the
ODS guys!

Cheers
Don