topic Re: prevent creating a new empty row at the bottom of the file in SAS Programming

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

EEEY — Tue, 30 Apr 2019 23:04:00 GMT

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

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

VDD — Wed, 01 May 2019 00:07:35 GMT

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.

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

ChrisNZ — Wed, 01 May 2019 00:13:49 GMT

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):

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

Tom — Wed, 01 May 2019 00:23:47 GMT

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.

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

Tom — Wed, 01 May 2019 03:17:45 GMT

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;

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

EEEY — Wed, 01 May 2019 05:22:25 GMT

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.

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

EEEY — Wed, 01 May 2019 05:31:00 GMT

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?

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

ErikLund_Jensen — Wed, 01 May 2019 07:59:19 GMT

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.

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

Tom — Wed, 01 May 2019 14:10:45 GMT

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

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

Ksharp — Wed, 01 May 2019 14:03:09 GMT

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;

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

EEEY — Wed, 01 May 2019 17:40:17 GMT

This is working and exactly what i needed..

Really appreciate your help Ksharp!

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

EEEY — Wed, 01 May 2019 17:43:29 GMT

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

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

Tom — Wed, 01 May 2019 17:52:15 GMT

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