how to create a trailer with in the column positions

[tvbMadhu]

Hi , 

Hi , 

I am working on a dataset to create an out put txt file with header and trailer records .I had created the Header and trailer ( count of total records). I want the header and trailer starts with first column and ends with the last column of the record .

Script:

%let filePath = /user/home/test;
libname test '/user/home/test/';
%include 'user/home/test/TDpwd.txt';

proc sql;
CONNECT TO TERADATA (user=&user password=&password server=oneview mode=teradata);
create table test.test_data as
select * from connection to teradata(
SELECT * from 5pt.test24
);
disconnect from teradata;
quit;

Data test.test_result;
set test.test_data;
file "&filePath/test.data_2016";
put
Name $ 1 - 17
City $ 18 - 25
Country $ 26 - 30
Id $ 31 - 46
;
run;

data test;
set test.test_data END=EOF;
file "&filePath/data_2016";

*Header record;
If _n_=1 then
do;
put 'AL,,,0000';
end;
put name city country id ;
/*Trailer record*/
if eof then
do;
put _N_ : z4.;
end;
run;

ex: Output :

AL16                                0000
Thomas New Jersy USA 1000
David     New Jersy USA 1000
Rahul     Virginia      USA 2002
DL99                               0003

Thanks

Madhu

[RW9]

You be able to do this simply with nobs= and end=, quite simply (I don't know what the AL or DL is though):

data have;
  name="Thomas"; city="New Jersy"; country="USA"; id=1000; output;
  name="David"; city="New Jersy"; country="USA"; id=1005; output;
run;

data _null_;
  set have nobs=num_obs end=last;
  file "s:\temp\rob\text.txt";
  if _n_=1 then put "AL16             0000";
  put Name $ 1 - 17
      City $ 18 - 25
      Country $ 26 - 30
      Id $ 31 - 46;
  tmp="DL99             "||strip(put(num_obs,z3.));
  if last then put tmp;
run;

Also, I would avoid file formats you come up with yourself, it just makes it headache for anyone using that file later on who may or may not know about your specific file setup.  Also, I don't see any extension given to the file in your program, I would call it .txt as that is what it is.

[tvbMadhu]

Thanks RW9 for your suggestions, It is working  but I would like to know  how to give the positioning for header and trailer as starting and ending at the last column of the records .Could you please let me know , if I have a .txt file with 40+ columns .

Ex : header : AL16 (starging on the column Name)  0000 ( ending on the column Id)

Trailer  DL99 ( starging below the column Name)    0003 (count of records - ending below the column Id)

if _n_=1 then put "AL16             0000";
  put Name $ 1 - 17
      City $ 18 - 25
      Country $ 26 - 30
      Id $ 31 - 46;
  tmp="DL99             "||strip(put(num_obs,z3.));

Thanks

Madhu

[RW9 · Accepted solution]

Ah, ok, use the @ to position the pointer at the place of the last variable:

data have;
  name="Thomas"; city="New Jersy"; country="USA"; id="1000"; output;
  name="David"; city="New Jersy"; country="USA"; id="1005"; output;
run;

data _null_;
  set have nobs=num_obs end=last;
  file "s:\temp\rob\text.txt";
  if _n_=1 then put @1 "AL16" @31 "0000";
  put Name $ 1 - 17
      City $ 18 - 25
      Country $ 26 - 30
      Id $ 31 - 46;
  tmp=strip(put(num_obs,z3.));
  if last then put @1 "DL99" @31 tmp;
run;
  

[tvbMadhu]

Awesome !! Thanks RW9 its working..

Thanks