BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PhilfromGermany
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

18 REPLIES 18
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

jakarman
Barite | Level 11

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 --<-----
PhilfromGermany
Fluorite | Level 6

@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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

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

PhilfromGermany
Fluorite | Level 6

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

art297
Opal | Level 21

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;

PhilfromGermany
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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

data_null__
Jade | Level 19

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

PhilfromGermany
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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

data_null__
Jade | Level 19

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.

PhilfromGermany
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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