Hi, I am using ods csvall to export a SAS data set to csv. The problem is, at the end of the file SAS (or Windows) inserts a blank row. I think this row is just the result of Windows adding DOS carriage control characters (CR and LF). Is there anyway to prevent this from happening? My code: ods csvall file = "&csv_path.\MyFile.csv" encoding=utf8 options(delimiter=';'); proc print data=work.output noobs; run; ods csv close; I've tried this using proc export or a simple data step with a filename statement, but I get the same result 😞 Thanks!
I tested your program on UNIX and also got a blank row. You complained about CSVALL on Aug 13 and the suggestions were to use something else. Seems like a good idea today too.
Hi,
I don't get a blank row with this code:
data _null_;
set sashelp.class;
file "s:\temp\rob\rr.csv" dlm=";";
put name sex age;
run;
Or did you mean the last row finishes with 0d and 0a?
What do you mean by inserts a blank row? Has your cursor in some editor moved there or did you debugged the csv-file as some binary viewer, the real content.
remember you have defined an utf-8 encoding not latin1 ascii.
@Jaap, when opening the file in Notepad, there is a blank line after the last line of output. When using CSVALL, there are two blank lines. Proc export and data steps using a filename statement produce only one line. One line is better than two lines. No line is better than one line. If having one line is a "Windows thing" then I guess there is no way around it and I'm just going to have to deal with it. Regards Phil
I think you will find that there is no blank line. At the end of each row of data you will get a '0D' '0A' character which is a linefeed combo. This doesn't mean there is another row just that it is the end of the current row. Notepad is trying to be helpful by putting the cursor on the next row as that is where it thinks you would start.
I tested your program on UNIX and also got a blank row. You complained about CSVALL on Aug 13 and the suggestions were to use something else. Seems like a good idea today too.
@data_null_ Could you possibly comment the code (starting from line 4)? Thanks!
Phil,
I'm not data_null_, but liked and slightly modified his code to that shown below. As per your question, the link statement "Directs program execution immediately to the statement label that is specified and, if followed by a RETURN statement, returns execution to the statement that follows the LINK statement." In the present case DN created a label called names:.
The code under the names: label writes out the variable names. The part I changed was to stop the code from writing out the variable names for _N_, _ERROR_ and _NAME_
filename FT66F001 '~/sashelp.csv' lrecl=256;
data _null_;
set sashelp.class;
file FT66F001 dsd encoding=utf8 delimiter=';' termstr=crlf;
if _n_ eq 1 then link names;
put (_all_)(:);
return;
names:
length _name_ $32;
do while(1);
call vnext(_name_);
if upcase(_name_) not in ('_N_','_ERROR_','_NAME_') then put _name_ @;
if upcase(_name_) eq '_NAME_' then leave;
end;
put;
return;
run;
Hi Arthur, is there a way to use a text qualifier for some columns, using your code? My output didn't look right, using it exactly like that. Also, when using put (_all_) what is the purpose of using (:)? @RW9, you're probably right. Still, I gotta figure out a way to eliminate at least that second blank line.
When you use the construct (variable list) (format list) in a PUT statement you need to have at least one format in the format list. Using the : format modifier "counts" and the PUT statement will use the default format for the variable hence it does not change how the variable's value is displayed..
What version of SAS are you using where the SAS automatic variables _ERROR_ and _N_ are not at the end of the PDV?
Ok, so the reason why the output file looked weird was because the record length was too short. I have one very long variable ($800.) so I had to set the record length to 800 as well. Data _null_, would you argue that Arthur's modifications are not necessary? I ran your code and it works well. Also, no blank lines, just the one that Notepad creates automatically when opening the file. I still don't fully understand the code (vnext, link names, ...), which is on of the reasons I always revert back to simple procedures like proc export or just using ODS CVS, but I'm going to try to research these statements and figure out why they are being used. Thanks for everyone's input. Regards Phil
Use the version I posted below that uses PROC TRANSPOSE and two separate data steps. It might be less confusing.
Everything I used in the program if fully documented and you should read that documentation to better understand the features of the program. The way the code is structured and the reasons for that are not specifically document with regards to this particular application. The purpose of the program is to write a CSV much the same way as your ODS CSVALL or PROC EXPORT, where all you really do is specify the name of the data set and the variables are written in the same order they are defined in the input data.
In order to achieve that CALL VNEXT is used find the names of the variable in the PDV. In this program because the SET statement if the first statement to define any variables the variable names of interest are located at the beginning of the PDV. You could verify this with PUT _ALL_;
We also want to use the "SAS Variable List" _ALL_ in the PUT statement to write the values read from each observation. PUT (_ALL_)(:); The placement of this statement is important because _ALL_ in this case means ALL variables that exist in the PDV, at the time the statement is compiled, sans SAS automatic variables, i.e. _N_, _ERROR_; That is the reason we need LINK so the variable(S) created to examine the PDV and return the names _NAME_ will not be in the PDV when SAS "sees" and compiles PUT (_ALL_)(=);
The two RETURNS are needed to 1) keep the data step from executing the LINKED statement on every observation. and 2) to send the data step back to the statement just after the LINK. And the statement label NAMES: if the "target" of the link.
I think this is a nice example of the power of the SAS Variable List, and a fare example of a use for VNEXT.
Data_null_, you solution works, so I just went with that. Thanks for everyone's input!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.