Desktop productivity for business analysts and programmers

how to create a trailer with in the column positions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

how to create a trailer with in the column positions

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


Accepted Solutions
Solution
‎11-29-2016 06:52 PM
Super User
Super User
Posts: 7,668

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

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;
  

View solution in original post


All Replies
Super User
Super User
Posts: 7,668

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

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.

 

Occasional Contributor
Posts: 8

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

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

Solution
‎11-29-2016 06:52 PM
Super User
Super User
Posts: 7,668

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

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;
  
Occasional Contributor
Posts: 8

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

Awesome !! Thanks RW9 its working..

Thanks
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 228 views
  • 0 likes
  • 2 in conversation