Delimited Export, File Length 9.1

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Delimited Export, File Length 9.1

Hey there,

First time ever posting.  I'm not sure if I'm posting in the right place so apologies if I'm not.

I've run into a roadblock that I cannot seem to work around and I am hoping someone will be able to help me.

Requirements:

     SAS 9.1

     Delimited Export File using tilde as the delimiter and no headers.

     Record length of at least 1500 columns (Characters)

     Extension must be something other than CSV (.t00)

Problem:

     I have one column in my dataset that has up to 800 characters.  Whenever this column forces a record to go over 256 characters, it forces the rest of the row to drop to the next line.

     Since this is a load file, I need that line to be extended.  I have read up on found that LRECL=2000 should have done this for me, placed on either the FILE or FILENAME statement (I have it on both, but I have tried multiple permutations of this.)  I have also found that it is possible that LRECL might only work in 9.2 for what I'm trying to do?  Unfortunately that upgrade is not happening for some time.

My Code:

%MACRO stripHeader(filename,dataset);

     FILENAME dlmFile &filename LRECL=2000;

     DATA _NULL_;

          SET &dataset;

          FILE dlmFile DSD LRECL=2000 DLM='~';

          PUT(_ALL_)(+0);

     RUN;

%MEND stripHeader;

Thank you in advance for any assistance that can be offered here!

John


Accepted Solutions
Solution
‎11-18-2012 02:56 PM
Super User
Super User
Posts: 6,499

Re: Delimited Export, File Length 9.1

How many lines do the SAS notes for the data _null_ step say were output to the file? If it matches the number of observations read from the dataset then you probably have an embedded end of line character in one of the variables.

Also check if LRECL=2000 really long enough for all of the variables. Remember to count the space for the delimiters.

View solution in original post


All Replies
Solution
‎11-18-2012 02:56 PM
Super User
Super User
Posts: 6,499

Re: Delimited Export, File Length 9.1

How many lines do the SAS notes for the data _null_ step say were output to the file? If it matches the number of observations read from the dataset then you probably have an embedded end of line character in one of the variables.

Also check if LRECL=2000 really long enough for all of the variables. Remember to count the space for the delimiters.

Contributor
Posts: 23

Re: Delimited Export, File Length 9.1

Tom, thank you - I meant to include the log notes but I completely forgot:

NOTE:  664 records were written to the file DLMFILE.

     The minimum record length was 663.

     The maximum record length was 927.

NOTE:  There were 664 observations read from the data set WORK.TEST_DATA_SET.

I'll look and see if there are any line feed characters in the free form 800 character field.  Thanks for that thought.  Will update this with what I find.

John

Contributor
Posts: 23

Re: Delimited Export, File Length 9.1

Okay!  The problem actually came down to two pieces.  There were line feed characters somehow in the data.  I stripped those out and the characters were no longer dropping to the next line.  Tom, thank you very much for that.

However, I found out the next step in code is actually causing some issue.  When I look at the file stored in the repository it looks normal  (Thanks to your help on the line feed).  When it is e-mailed out, however, everything strips down to 256 characters for every single line.

So, when I open the e-mail, every single record is stopped at 256.  When I look on the server before it is e-mailed out, the file is as it should be.

My Code:

%MACRO stripHeader(filename,dataset);

     FILENAME dlmFile &filename LRECL=2000;

     DATA _NULL_;

          SET &dataset;

          FILE dlmFile DSD LRECL=2000 DLM='~';

          PUT(_ALL_)(+0);

     RUN;

     FILENAME fileEmail email

          to = 'someaddress@yadda.com'

          ATTACH=&filename

          LRECL=2000;

     DATA _NULL_;

          FILE fileEmail LRECL=2000;

     RUN;

%MEND stripHeader;

Thanks again!

Contributor
Posts: 23

Re: Delimited Export, File Length 9.1

Below is the corrected code. 

I found it here:

http://support.sas.com/documentation/cdl/en/hostunx/61879/HTML/default/viewer.htm#email.htm

"ATTACH='filename.ext' | ATTACH = ('filename.ext' <attachment-options>)

specifies the physical name of the files to be attached to the message and any options to modify attachment specifications. Enclose filename.ext in quotation marks. To attach more than one file, enclose the group of filenames in parentheses. For example, attach='/u/userid/opinion.txt'and attach=("june98.txt" "july98.txt") are valid file attachments.

By default, SMTP e-mail attachments are truncated at 256 characters. To send longer attachments, you can specify the LRECL= and RECFM= options from the FILENAME statement as the attachment-options. For more information about the LRECL= and RECFM= options, see FILENAME Statement: UNIX."

Thank you very much for putting me in the right path, Tom!

%MACRO stripHeader(filename,dataset);

     FILENAME dlmFile &filename LRECL=2000;

     DATA _NULL_;

          SET &dataset;

          FILE dlmFile DSD LRECL=2000 DLM='~';

          PUT(_ALL_)(+0);

     RUN;

     FILENAME fileEmail email

          to = 'someaddress@yadda.com'

         ATTACH=(&filename LRECL=2000);

     DATA _NULL_;

          FILE fileEmail;

     RUN;

%MEND stripHeader;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 347 views
  • 0 likes
  • 2 in conversation