BookmarkSubscribeRSS Feed
BlackTea
Calcite | Level 5

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!

9 REPLIES 9
Tom
Super User Tom
Super User

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?

Tom
Super User Tom
Super User

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?

jaredp
Quartz | Level 8

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

ballardw
Super User

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

Tom
Super User Tom
Super User

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_) (:) ;

run;

BlackTea
Calcite | Level 5

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!

atesera
Calcite | Level 5

Hi @Tom ,

in regard to your comment about this thread "The following program can easily be modified to add the variable headers in a number of ways."

could you please share a sample how I can do that. I am getting the file as csv but no headers at all. I need the headers to be in place for the output.

Thanks in advance.

 

Tom
Super User Tom
Super User

Please start a new question. Post an example of the file you have and what you want to get instead. If the original file does not have headers then what is the source for the header line you want to add?

 

See this answer on another more recent thread. https://communities.sas.com/t5/SAS-Programming/Exporting-dataset-to-csv-filename-issues/m-p/916051/h...

 

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
  • 9 replies
  • 7206 views
  • 9 likes
  • 5 in conversation