prevent creating a new empty row at the bottom of the file

[EEEY]

Hi, 

I am creating a Pipe delimited CSV file in SAS EG. Everything looks fine only except the end of the file, it created a empty row. Can anyone help me out. My program is like this:

%MACRO OUTPUT_HEADER(COUNT=);
%DO I = 1 %TO &COUNT;
H&I +(-1) '|'
%END;
%MEND OUTPUT_HEADER;

DATA _null_;
SET test ;
File "&OUT/test_20190429.CSV" dlm='|' dsd lrecl=800;

If _N_=1 then Do;
H1 = 'ClientID';
H2 = 'RegionID';
H3 = 'Name';

Put %OUTPUT_HEADER(COUNT=2) H3;
End;

Put ClientID +(-1) '|'
RegionID +(-1) '|'
Name;
Run;

For some reason, there is always one empty rows at the bottom of file.

Any suggestion?

Thanks,

EEEY

[VDD]

set eof to only output when false.

eof = end of file

or test if a variable is null and if so then do not output record.

[ChrisNZ]

It must be something to do with your data.

This does not add empty rows:

data _null_;
  set SASHELP.CLASS(obs=4) ;
  file "%sysfunc(pathname(WORK))/test.csv" dlm='|' dsd lrecl=800;
  put SEX +(-1) '|' AGE +(-1) '|' NAME;
run;

data _null_; 
 infile "%sysfunc(pathname(WORK))/test.csv";
 input; 
 put _infile_; 
run;

NOTE: The infile "X:\SASWORK\_TD4320_NZ8037SPSAS2003_\Prc2/test.csv" is:
Filename=X:\SASWORK\_TD4320_NZ8037SPSAS2003_\Prc2\test.csv,
RECFM=V,LRECL=32767,File Size (bytes)=51,
Last Modified=01 May 2019 12:13:04,
Create Time=01 May 2019 12:06:24

1 M|14|Alfred
2 F|13|Alice
3 F|13|Barbara
4 F|14|Carol
NOTE: 4 records were read from the infile "X:\SASWORK\_TD4320_NZ8037SPSAS2003_\Prc2/test.csv".
The minimum record length was 10.
The maximum record length was 12.
NOTE: DATA statement used (Total process time):

[Tom]

That code should not write any empty lines. Even if the last observations had all empty values it would still write the delimiters.

Make sure that it is not just that the program you are using to open the file is confused and doesn't understand that all lines in a text file end with end of line characters, even the last one.  Some programs get confused and think they are reading a binary file that has "record separators" instead of a text file that has end of line markers.

[Tom]

Your program is way more complicated than it needs to be.

data _null_;
  set test ;
  file "&OUT/test_20190429.CSV" dlm='|' dsd ;

  if _N_=1 then do;
    H1 = 'ClientID';
    H2 = 'RegionID';
    H3 = 'Name';
    put h1-h3;
  end;

  put ClientID RegionID Name;
run;

[EEEY]

Thanks Tom. Yes thanks for simplying my code. Basically I have 70+ fields in my original program.. After the simplified version, I still have the same problem.

[EEEY]

I need to rephrase the issue. 

the file looks fine, only except the cursor ends at the 1st position of next row of data if opening it in TextPad. . I wanted the cursor to end at the last position of last row of data, and not going to the next row. 

Any suggestions?

[ErikLund_Jensen]

A simple SAS  Put statement with no modifiers puts each line with an end-of-line mark, and number of lines in the file is the number of end-of-line marks.

A different thing is what you see in the editor. Consider this example:

145  data _null_;
146      file 'c:\temp\test.csv';
147      put 'Hansen';
148      put 'Jensen';
149  run;

NOTE: The file 'c:\temp\test.csv' is:
      Filename=c:\temp\test.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=0,
      Last Modified=01. maj 2019 09:52:48,
      Create Time=01. maj 2019 09:41:15

NOTE: 2 records were written to the file 'c:\temp\test.csv'.
      The minimum record length was 6.
      The maximum record length was 6.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


150
151  data _null_;
152      infile 'c:\temp\test.csv';
153      input navn$;
154  run;

NOTE: The infile 'c:\temp\test.csv' is:
      Filename=c:\temp\test.csv,
      RECFM=V,LRECL=32767,File Size (bytes)=16,
      Last Modified=01. maj 2019 09:52:48,
      Create Time=01. maj 2019 09:41:15

NOTE: 2 records were read from the infile 'c:\temp\test.csv'.
      The minimum record length was 6.
      The maximum record length was 6.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

There are 2 lines written to the file, and 2 lines read from the file, so it is verified that the file contains 2 lines. But if you open the file in an editor, you see:

So it seems that your code works, and the confusion stems from the editor's way of displaying the file. 

[Tom]

---

@EEEY wrote:

I need to rephrase the issue. 

the file looks fine, only except the cursor ends at the 1st position of next row of data if opening it in TextPad. . I wanted the cursor to end at the last position of last row of data, and not going to the next row. 

Any suggestions?

---

Why do you care that TextPad displays the file that way? 

Note that TextPad is an EDITOR.  It has decided to give you a place to add characters after the end of the file by displaying an extra line number.

[EEEY]

Just my vendor is very picky about it and need the cursor stops at the end of data. with the code below, If i open it in TextPad, the cursor ends at the last position when you click the blank area in the file.
Thanks so much for your comments!!!

[Tom]

---

@EEEY wrote:
Just my vendor is very picky about it and need the cursor stops at the end of data. with the code below, If i open it in TextPad, the cursor ends at the last position when you click the blank area in the file.
Thanks so much for your comments!!!

---

What is the VENDOR?  Is it for some software you are going to load the file into? What system?

Did you explain to the vendor that lines in text files end with end of line markers.  How does their system know you haven't sent a truncated file if the last line does not have a proper ending?

The only way to prevent the proliferation of this type of interoperabilty malfunctions is to publicly shame them. At least get them to admit that their required file format is neither a CSV (delimited) file nor is even a properly formatted TEXT file.

[Ksharp · Accepted solution]

How about this one ?

data _null_;
 set sashelp.class end=last;
 file 'c:\temp\x.csv' dsd recfm=n;
 put (_all_) (:) ;
 if not last then put '0D0A'x;
run;

[EEEY]

This is working and exactly what i needed.. 

Really appreciate your help Ksharp!