The SAS Output Delivery System and reporting techniques

Don't insert blank line in csv file

Reply
Occasional Contributor
Posts: 10

Don't insert blank line in csv file

Hi,

I use proc export to write a csv file which will be uploaded into another system. The problem is that this csv file gets a blank line at the end, this troubles this other system. I tried to write this file with ods csv tagset, but then even two blank lines are inserted. I modified the csv tagset in the event table and accomplished to avoid one of the blank lines. I found the place from where the second line is coming, but I don't know how to modify that this blank line will not be written.

I've also tried with file and put statement in a data step, but one blank line is added as well.

Here is my proc export:

proc export data = test

     outfile = "Z:\temp\\test.csv"

     dbms = dlm

     replace;

     delimiter = ",";

run;


Here is my export with the modified ods csv tagset. The remaining blank line is written during the row event by put NL; which is appended to each written row to start a new line for the next row, which I don't need for the last row.


proc template;

  define tagset

    tagsets.csvnoq / store=sasuser.templat;

    parent=tagsets.csv;

    define event quotes;

    end;

    define event table;                                                    

    end;

                                                                             

    define event row;                                                                                                                                

       finish:                                                             

          do /if $cell_count;                                              

          close;                                                           

          eval $cell_count 0;                                              

                                                                         

          else /if $$row;                                                     

            put $$row;                                                       

            unset $$row;                                                     

          done;                                                               

                                                                         

          put NL;                                                             

    end;

  end;

run;

ods tagsets.csvnoq file = "Z:\temp\test.csv";

    data test;

         do var1 = 1 to 100;

            var2 = var1;

            output;

         end;

    run;

    proc print noobs data=test;

         var var1 var2;

    run;

ods tagsets.csvnoq close;


I hope someone has an idea what else I can try or how to get rid of the blank line in the last row via the csv tagset.

Many thanks!

Super User
Super User
Posts: 6,850

Re: Don't insert blank line in csv file

Why would you want to use ODS to create a CSV file? 

Are you trying to create a CSV file from a report generated by PROC REPORT or some other PROC?

Super User
Super User
Posts: 6,850

Re: Don't insert blank line in csv file

Note that PROC EXPORT does NOT insert any blank lines at the end of the file.  Try running this simple program to see that.

filename out temp;

proc export data = sashelp.class(obs=1)

     outfile = out

     dbms = dlm

     replace;

     delimiter = ",";

run;

data _null_;

  infile out recfm=f lrecl=80;

  input ;

  list;

run;

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-

1   CHAR  Name,Sex,Age,Height,Weight..Alfred,M,14,69,112.5.. 50

    ZONE  46662567246624666672566667004667662423323323332300

    NUMR  E1D5C358C175C859784C759784DA1C6254CDC14C69C112E5DA

I ran this on Windows so you will see that the last characters are the Carriage Return and Line Feed (0D0A that mark the end of the last line.

Perhaps your down stream application is using some strange file processing where it interprets this to mean that there is a blank line?

Contributor
Posts: 71

Re: Don't insert blank line in csv file

Perhaps use the END option in your SET statement in your datastep.  Then do you can use an IF statement to either put NL or not put NL accordingly.

See this for an example:

I'd also suggest that you double check what this other system expects.  *Nix type systems typically use Line Feeds whereas the Windows standard is CR and LF.  This can cause "hiccups".

Text file encoding can also cause problems.  Your CSV file is likely UTF8, but perhaps your other system is expecting a different charset (like Latin1 or something).

I meant to reply to @BlackTea Message was edited by: Jared Prins

Super User
Posts: 11,141

Re: Don't insert blank line in csv file

Is the receiving application running on UNIX or some variation thereof? If so this may be a typical Windows/DOS file conversion issue.

Super User
Super User
Posts: 6,850

Re: Don't insert blank line in csv file

We had this discussion before, but I cannot find it.

The trick is to use RECFM=N and manually insert the end of line characters where you want them.

The following program can easily be modified to add the variable headers in a number of ways.

data _null_;

  file out dsd recfm=n ;

  set sashelp.class(obs=2);

  if _n_ > 1 then put '0D0A'x ;

  put (_all_) (Smiley Happy ;

run;

Occasional Contributor
Posts: 10

Re: Don't insert blank line in csv file

I tried to use ods csv because all the other things didn't work, so I also tried ods csv and thought that it would be the best way to modify this tagset to get my result. But I was wrong Smiley Happy

I know that proc export doesn't really insert an empty blank data line. But SAS on a Windows system writes a line feed and a carriage return (hex = 0D 0A) at the end of each line. Which is perfectly correct. So it happens that at the end of the file there is an empty line displayed because of the carriage return, and I guess that the system to which the file is being uploaded is maybe not a Windows system, thus it misinterprets this last "empty" line as an empty data line.

But, I managed to create this file without this last LF and CR by combining your proposal and this one here:

https://communities.sas.com/thread/14211?start=0&tstart=0


data _null_;

     file "Z:\temp\test.csv" recfm=n;

     length row $1000;

     set test ( keep = var1 var2 var3 var4 );

     row = catx( ",", var1, var2, var3, var4 );

     if _n_ = 1 then put "var1" "," "var2" "," "var3" "," "var4" "0D0A"x;

     if _n_ > 1 then put "0D0A"x;

     put row;

run;

Thanky very much!

Ask a Question
Discussion stats
  • 6 replies
  • 2602 views
  • 9 likes
  • 4 in conversation