topic Re: how to create a trailer with in the column positions in SAS Enterprise Guide

how to create a trailer with in the column positions

tvbMadhu — Mon, 28 Nov 2016 15:43:12 GMT

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

Re: how to create a trailer with in the column positions

RW9 — Mon, 28 Nov 2016 15:54:53 GMT

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.

Re: how to create a trailer with in the column positions

tvbMadhu — Mon, 28 Nov 2016 17:01:39 GMT

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

Re: how to create a trailer with in the column positions

RW9 — Tue, 29 Nov 2016 11:07:09 GMT

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;

Re: how to create a trailer with in the column positions

tvbMadhu — Tue, 29 Nov 2016 23:54:00 GMT

Awesome !! Thanks RW9 its working..

Thanks