Export to csv using ods causing blank row at the bottom of csv file

[atesera]

Hi, 

I am exporting a sas dataset into csv using ods csv file statement and i am getting a complaint that the csv file has a blank row at the bottom. I have searched through the topics and found one which is very similar to my issue but when use that the csv file is created without column headers. I need to display column headers but i couldnt find a way how to do that.

any help is appreciated

current process below - delta is the SAS table that I am trying to export into csv with headers in it

/* export to csv */

%let rundate = %SYSFUNC(today(),YYMMDDN.);
%put rundate is &rundate;

ods listing close;
ods csv file="c\Data_&rundate..csv";
proc print data=delta noobs label;
run;
ods csv close; 

But when I do this, recipient says there is a blank row (which I dont see anywhere) at the bottom of the csv file which causes issue on their end.

So, I found this topic and solution in the SAS forums but when I use the code below, the csv file that is generated is missing the headers. I need the headers to be in the csv file as well. Thanks in advance.

[Kurt_Bremser]

Use PROC EXPORT:

proc export
  data=your.dataset
  file="/complete/path/to/file.csv"
  dbms=csv
  label
  replace
;
run;

[atesera]

Thanks. I tried that and now I can see the column headers. I hope the blank row issue at the bottom of the csv file will be solved by using proc export instead of using ods option for export. 

[Kurt_Bremser]

On top of what has already been said, ODS CSV plus PROC PRINT is the slowest way to write a csv file, while the DATA step (and that is what PROC EXPORT creates) is the fastest.

[Tom · Accepted solution]

The simplest thing is not use PROC PRINT to make a CSV file.

You can use PROC EXPORT instead.

proc export data=delta file="c\Data_&rundate..csv" replace dbms=csv label;
run;

[atesera]

Thanks. yes that worked and now I have the column headers in the csv file. Hope proc export step eliminates this blank row issue at the bottom of csv file. Previously I was using ods to export to csv and I guess that might be the issue. 

[Tom]

---

@atesera wrote:

Thanks. yes that worked and now I have the column headers in the csv file. Hope proc export step eliminates this blank row issue at the bottom of csv file. Previously I was using ods to export to csv and I guess that might be the issue. 

---

It should work. The ODS CSV tool definitely adds an actual extra empty line that PROC EXPORT does not.

The issue in the old topic you linked to was about creating a file that removed the end of line characters from the last actual line of data in the file. Hopefully you are sending your file to a program that actually understands how lines are formed in text files and does not expect the final line to stop before the end of line characters have been written to the file.

[atesera]

I haven't yet heard back from the team that loads the file after I switched to proc export instead of ODS csv tool. The reason I was using ODS csv was that I needed very long (over 32 characters) column headers to be displayed in the output file and ODS csv was doing that. But I think I didn't use the "label" option in my proc export step previously so now I use it and all looks good. Thanks for advice and help.

[Ksharp]

/*You could use the following code to output the variable name at first place.*/
data have;
 set sashelp.class;
run;


/*Get variable's name*/
proc sql noprint;
select name into :names separated by ','
 from dictionary.columns
  where libname='WORK' and memname='HAVE';
quit;
data _null_;
 set have end=last;
 file 'c:\temp\temp.csv' dsd recfm=n;
 if _n_=1 then put "&names." '0D0A'x;
 put (_all_) (:);
 if not last then put '0D0A'x;
 run;

[atesera]

Thanks for this. However, the issue with this is that I have to use labels to display in the output csv file. I cant name the column headers in the way that I want because they are very long and doesn't allow me. So, I found the solution of labelling them in the way that I want because in the label there is no restrictions like that. 

So, I cant put the labels here then I get error message saying it is too long and plus I have over 200 columns in my dataset

"&names."

 I believe that proc export step with label option would eliminate the issue and will update this topic once I hear back from the team uploading the output file into the system

[Tom]

If you need headers that are longer than SAS labels allow (currently 256 bytes) then you could also just use a separate dataset for writing the header row.

Create a dataset, let's call it HEADERS, with a variable name, let's call it HEADER, that has observations in the same order as the variables appear in your dataset.  Now you can write the CSV file in two steps.

First write the header line:

data _null_;
  set headers;
  file csv dsd ;
  put header @;
run;

Then use the MOD option on the FILE statement to append the data lines.

data _null_;
  set have;
  file csv dsd mod;
  put (_all_) (+0);
run;

[Ksharp]

That is easy to use variable LABEL instead of variable name.

data have;
 set sashelp.class;
 label name='asasf sdfsfe' weight='w sd erbd s';
run;


/*Get variable's label*/
proc sql noprint;
select quote(strip(label)) into :label separated by ','
 from dictionary.columns
  where libname='WORK' and memname='HAVE';
quit;
data _null_;
 set have end=last;
 file 'c:\temp\temp.csv' dsd recfm=n;
 if _n_=1 then put %sysfunc(quote(%bquote(&label.))) '0D0A'x;
 put (_all_) (:);
 if not last then put '0D0A'x;
 run;