I have the text file which is not uniform. I want to read in one variable in continues, which is separated by comma. Like below. Thanks for help.
have input:
abc,def,
ghi,jkl,mno
,pqr stuw,xyz
want ouput:
abc
def
ghi
jkl
mno
pqr stuw
xyz
So if you have the data in a file then read it using RECFM=N and manually remove or replace the end of line characters.
You will need to know what end of line characters your file is using. Normally it is either CR+LF (Windows/DOS) or LF (Unix).
Let's put your example data into a file with CR+LF as the end of line characters.
filename sample temp;
data _null_;
file sample termstr=CRLF;
put
'abc,def'
/'new line,'
/'ghi,jkl,mno'
/',pqr'
/'stuw,xyz'
;
run;
Now let's read it in.
data test;
length word $100 ;
infile sample dsd recfm=n ;
input word ;
if word=:'0D0A'x then word=substr(word,3);
word = tranwrd(word,'0D0A'x,' ');
put _n_= word= :$quote.;
run;
Results.
_N_=1 word="abc" _N_=2 word="def new line" _N_=3 word="ghi" _N_=4 word="jkl" _N_=5 word="mno" _N_=6 word="pqr stuw" _N_=7 word="xyz"
you need to use double trailing @@
data want;
informat var $20.;
infile datalines dsd ;
input var $ @@;
if var = ' ' then delete;
datalines;
abc,def,
ghi,jkl,mno
,pqr stuw,xyz
,hhhhhhhhhhhhhhhh,yyy
;
run;
thank you kiranV_. it works..
/*with single trailing*/
data have1;
infile datalines dsd truncover;
input var :$20. @;
do _n_=1 by 1 until(_n_ eq countw(_infile_,',','M'));
if not missing(var) then output;
input var : $20. @;
end;
datalines;
abc,def,
ghi,jkl,mno
,pqr stuw,xyz
;
/*with scan*/
data have2;
infile datalines dsd truncover;
input @;
do _n_=1 to countw(_infile_,',');
var=scan(_infile_,_n_,',');
if not missing(var) then output;
end;
datalines;
abc,def,
ghi,jkl,mno
,pqr stuw,xyz
;
thank you all dear expert.
I apologies, I miss a condition: the "new line " (enter key) if present I wanted to translate to space (" ") so that it reads in as continues seperated by comma.
for example:
have:
abc,def
new line,
ghi,jkl,mno
,pqr
stuw,xyz
want:
abc
def new line
ghi
jkl
mno
pqr stuw
xyz
this might work.
data want;
informat var $20.;
infile datalines dsd ;
input var $ @@;
datalines;
abc,def,
ghi,jkl,mno
,pqr stuw,xyz
,hhhhhhhhhhhhhhhh,yyy
;
run;
So if you have the data in a file then read it using RECFM=N and manually remove or replace the end of line characters.
You will need to know what end of line characters your file is using. Normally it is either CR+LF (Windows/DOS) or LF (Unix).
Let's put your example data into a file with CR+LF as the end of line characters.
filename sample temp;
data _null_;
file sample termstr=CRLF;
put
'abc,def'
/'new line,'
/'ghi,jkl,mno'
/',pqr'
/'stuw,xyz'
;
run;
Now let's read it in.
data test;
length word $100 ;
infile sample dsd recfm=n ;
input word ;
if word=:'0D0A'x then word=substr(word,3);
word = tranwrd(word,'0D0A'x,' ');
put _n_= word= :$quote.;
run;
Results.
_N_=1 word="abc" _N_=2 word="def new line" _N_=3 word="ghi" _N_=4 word="jkl" _N_=5 word="mno" _N_=6 word="pqr stuw" _N_=7 word="xyz"
thank you Tom. I got the expected. and learn many thing from experts.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.