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
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;
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.
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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.