topic Re: Don't insert blank line in csv file in ODS and Base Reporting

Don't insert blank line in csv file

BlackTea — Wed, 10 Jul 2013 13:53:02 GMT

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;

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!

Re: Don't insert blank line in csv file

Tom — Wed, 10 Jul 2013 14:03:08 GMT

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?

Re: Don't insert blank line in csv file

Tom — Wed, 10 Jul 2013 14:18:26 GMT

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?

Re: Don't insert blank line in csv file

ballardw — Wed, 10 Jul 2013 15:39:03 GMT

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

Re: Don't insert blank line in csv file

jaredp — Wed, 10 Jul 2013 15:46:57 GMT

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

Re: Don't insert blank line in csv file

Tom — Wed, 10 Jul 2013 16:11:55 GMT

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;

Re: Don't insert blank line in csv file

BlackTea — Thu, 18 Jul 2013 07:04:23 GMT

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

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!

Re: Don't insert blank line in csv file

atesera — Wed, 14 Feb 2024 13:49:22 GMT

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.

Re: Don't insert blank line in csv file

Tom — Wed, 14 Feb 2024 14:20:15 GMT

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/highlight/true#M360897

Re: Don't insert blank line in csv file

atesera — Wed, 14 Feb 2024 14:37:01 GMT

okay thanks. I did here is the screenshot and below is the link

https://communities.sas.com/t5/ODS-and-Base-Reporting/Export-to-csv-using-ods-causing-blank-row-at-the-bottom-of-csv/td-p/916065