Help using Base SAS procedures

DOS Carriage Return and Line Feed in CSV files

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

DOS Carriage Return and Line Feed in CSV files

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 Smiley Sad Thanks!


Accepted Solutions
Solution
‎09-22-2014 10:12 AM
Respected Advisor
Posts: 3,799

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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.

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_)(Smiley Happy;
   return;
names:
  
length _name_ $32;
  
do while(1);
      call vnext(_name_);
      if upcase(_name_) eq '_NAME_' then leave;
      put _name_ @;
      end;
  
put;
  
return;
  
run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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?

Trusted Advisor
Posts: 3,215

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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.

---->-- ja karman --<-----
Contributor
Posts: 39

Re: DOS Carriage Return and Line Feed in CSV files

@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

Super User
Super User
Posts: 7,977

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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.

Solution
‎09-22-2014 10:12 AM
Respected Advisor
Posts: 3,799

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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.

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_)(Smiley Happy;
   return;
names:
  
length _name_ $32;
  
do while(1);
      call vnext(_name_);
      if upcase(_name_) eq '_NAME_' then leave;
      put _name_ @;
      end;
  
put;
  
return;
  
run;

Contributor
Posts: 39

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to data_null__

@data_null_ Could you possibly comment the code (starting from line 4)? Thanks!

PROC Star
Posts: 7,487

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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

Contributor
Posts: 39

Re: DOS Carriage Return and Line Feed in CSV files

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 (Smiley Happy? @RW9, you're probably right. Still, I gotta figure out a way to eliminate at least that second blank line.

Super User
Super User
Posts: 7,074

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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..

Respected Advisor
Posts: 3,799

Re: DOS Carriage Return and Line Feed in CSV files

What version of SAS are you using where the SAS automatic variables _ERROR_ and _N_ are not at the end of the PDV?

Contributor
Posts: 39

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to data_null__

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

Super User
Super User
Posts: 7,074

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

Use the version I posted below that uses PROC TRANSPOSE and two separate data steps.  It might be less confusing.

Respected Advisor
Posts: 3,799

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to PhilfromGermany

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_)(Smiley Happy;  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.

Contributor
Posts: 39

Re: DOS Carriage Return and Line Feed in CSV files

Posted in reply to data_null__

Data_null_, you solution works, so I just went with that. Thanks for everyone's input!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 2674 views
  • 6 likes
  • 7 in conversation