07-10-2013 09:53 AM
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
delimiter = ",";
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.
tagsets.csvnoq / store=sasuser.templat;
define event quotes;
define event table;
define event row;
do /if $cell_count;
eval $cell_count 0;
else /if $$row;
ods tagsets.csvnoq file = "Z:\temp\test.csv";
do var1 = 1 to 100;
var2 = var1;
proc print noobs data=test;
var var1 var2;
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.
07-10-2013 10:03 AM
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?
07-10-2013 10:18 AM
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
delimiter = ",";
infile out recfm=f lrecl=80;
1 CHAR Name,Sex,Age,Height,Weight..Alfred,M,14,69,112.5.. 50
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?
07-10-2013 11:46 AM
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.
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
07-10-2013 12:11 PM
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.
file out dsd recfm=n ;
if _n_ > 1 then put '0D0A'x ;
put (_all_) ( ;
07-18-2013 03:04 AM
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:
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;
Thanky very much!