The SAS Output Delivery System and reporting techniques

ODS CSV 2 challenges

Reply
Occasional Contributor
Posts: 12

ODS CSV 2 challenges

I am faced with two challenges in creating ODS output with SAS 9.3.   I am creating a mail merge data file with an ID column, then 6 additional columns with messages.   Everyone will have at least one message and some will have more than one up to the six, meaning lots of empty cells all around.   Some messages contain over 1024 characters.  The ID cell can have up to two leading zeroes. 

I have tried to create a modified csv tagset to deal with leading zeroes and that seems to work in a very simple query, but when I assign the adjusted templace to the ODC CSV process, the output is garbage.   Quotes start appearing in multiple columns and many columns are shifted.

If I use the plain ods csv process and add an options (quoted_columns='1'), then my column layout looks great, but I don't see leading zeroes appearing on IDs in that first column.   Trying to add this quoted_columns option to the modified tagset causes a crash.  I might not be adding it correctly, though.

I can't use ODS EXCELXP because of the 1024 character limitation.

Any ideas are appreciated.   Thanks!

Super User
Super User
Posts: 6,502

ODS CSV 2 challenges

Why are you using ODS to generate a CSV file?  Why not just use a data step.  Use the Z format to preserve the leading zeros.

Check out this very recent thread on SAS-L.

http://listserv.uga.edu/cgi-bin/wa?A2=ind1202C&L=sas-l&D=0&F=P&P=11485&F=

Occasional Contributor
Posts: 12

ODS CSV 2 challenges

I can overcome the challenges using proc export on my Windows desktop, yes.  I have been running this successfully at my desk.     I am trying to set this up to run in batch.    I thought I had to use ODS for this, but maybe I could use proc export with the sas exprt -noterminal command?

PROC Star
Posts: 7,366

ODS CSV 2 challenges

I think you only have to use the -noterminal option if you are running batch on Unix.

Super User
Posts: 17,958

ODS CSV 2 challenges

Won't a straight CSV export of the data work? Any reason for an ODS?

The leading zero get dropped if you open the CSV file in Excel, but not in the actual CSV file, depending on your output.

This will export a comma quoted file which should meet most requirements for a mail merge data file:

(Data Null's code originall)

data _null_;

   set sashelp.class;

   file log dsd;

   if _n_ eq 1 then link names;

   put (_all_)(~);

   return;

names:

   length _name_ $32;

   do while(1);

      call vnext(_name_);

      if upcase(_name_) eq '_NAME_' then leave;

      put _name_ ~ @;

      end;

   put;

   return;

   run;

Ask a Question
Discussion stats
  • 4 replies
  • 797 views
  • 0 likes
  • 4 in conversation