BookmarkSubscribeRSS Feed
Pylon53
Calcite | Level 5

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!

4 REPLIES 4
Tom
Super User Tom
Super User

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=

Pylon53
Calcite | Level 5

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?

art297
Opal | Level 21

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

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2296 views
  • 0 likes
  • 4 in conversation