DATA Step, Macro, Functions and more

Working with text file with blank rows between fields

Accepted Solution Solved
Reply
Super Contributor
Posts: 400
Accepted Solution

Working with text file with blank rows between fields

Hello,

 

I have a text file that i'm importing into a sas table and that part works correctly. 

What I'm trying to do is create one line of data when the data is on 2 lines.

 

For example The data I want starts on row 8.  Everything below that is either 1 or 2 rows with a blank row inbetween the data.

 

Feb 15, 2012. Testing how data is working. www.test.com

 

Mar 16, 2013  Fake book entry. 

www.book.com

 

Jan 02, 2011  Test title. Frank Testing

www.titlestore.com

 

What I need the data in my table to look like:

 

Feb 15, 2012. Testing how data is working. www.test.com

Mar 16, 2013  Fake book entry. www.book.com

 Jan 02, 2011  Test title. Frank Testing. www.titlestore.com

 

Thank you

 

 


Accepted Solutions
Solution
‎04-22-2016 08:49 AM
Trusted Advisor
Posts: 1,118

Re: Working with text file with blank rows between fields

Posted in reply to jerry898969

Hello @jerry898969,

 

Would this be any good?

/* Create test data */

data have;
input string $80.;
cards;
Feb 15, 2012. Testing how data is working. www.test.com
. 
Mar 16, 2013  Fake book entry. 
www.book.com
. 
Jan 02, 2011  Test title. Frank Testing
www.titlestore.com
;

/* Concatenate lines that belong together */

data want;
length string $80 text $400; /* please adapt lengths as appropriate */
do until(string=' ' | last);
  set have end=last;
  text=catx(' ', text, string);
end;
drop string;
run;

View solution in original post


All Replies
Super User
Posts: 19,832

Re: Working with text file with blank rows between fields

Posted in reply to jerry898969

You should be able to use a standard input/infile read method. 

 

1. For data that starts on row 8 - firstobs=8

2. For multiple lines use either / or # to read the lines. I would consider each record 3 lines so that you can ignore the last record.

 

This is untested so likely buggy, but hopefully gets you started.

 

data want;
informat date anydtdte. title $40. URL $256.;
infile 'your path to file' dsd truncover;

input #1 date title $
         #2 URL $
         #3 Blank $;

run;
Super User
Posts: 11,343

Re: Working with text file with blank rows between fields

May need to add N=3 option on the INFILE to use @Reeza's suggestion.

Super Contributor
Posts: 400

Re: Working with text file with blank rows between fields

Thank you for the reply.

 

I was doing this in my table after I brought the data in.  I bring it in as it is in the text file.  The pattern I found was that in some cases there are 1 or 2 lines that make up the line and each are followed by a blank row.  I have used the retain statement and this code which seems to be working, but I'm having a hard time when to output the row once they are concatenated.

 

 

data raw_ ; 
	length temp $400;	
	set raw(firstobs=8) ;
	retain temp ;
	if text eq '' then temp = '' ;
	if text ne '' then do ;
		if length(temp) = 1 then temp = text ;
		else do ;
			temp = catx(' ', temp, text) ;			
		end ;
	end ;
run ;

 

Is it recommended to try and do this check on import? The reason I just bring in each line as it's own row is to allow me to use SAS to break the data up.  This process will run multiple times with different data.

 

Thank you.

Super User
Posts: 19,832

Re: Working with text file with blank rows between fields

Posted in reply to jerry898969

If your file structure is consistent across files you should fix it in the import step, rather than post processing. 

Super Contributor
Posts: 400

Re: Working with text file with blank rows between fields

It's not as consistant as I would like.  I think I have to do it post processing.  At least to start.

 

Is there a way to check ahead to see if the next row is blank then I could output at that point?

 

Thank you

Contributor
Posts: 39

Re: Working with text file with blank rows between fields

Posted in reply to jerry898969

I wrote a program to "look ahead" a little while back and modified it to work with your data as it appears here.  This will not work well if the lines are not separated by a blank line.  Anyway, the technique of reading the buffer and examining it before deciding to output the text may be useful.


%let linesize = 80;

data input_text (keep = text);

    infile datalines eof=eof;  * use eof=label    with datalines,
                                             use end=variable with an input file;
    length buffer text  $  &linesize;
    retain text;

    input buffer $ 1-&linesize; * Read the next input line;

    if (strip(buffer) = '') then do;
 
eof:    put 'buffer empty or eof so output "' text '"';

        if (strip(text) ^= '') then
             output;
        text = '';
    end;
    else do;
        put 'buffer not empty so append "' buffer '" to text';

        text = catx(' ', text, buffer);
    end;

datalines;
Feb 15, 2012. Testing how data is working. www.test.com

Mar 16, 2013  Fake book entry.
www.book.com

Jan 02, 2011  Test title. Frank Testing
www.titlestore.com
;
*run; * RUN not needed when reading datalines;
/*
From log:

buffer not empty so append "Feb 15, 2012. Testing how data is working. www.test.com " to text
buffer empty or eof so output "Feb 15, 2012. Testing how data is working. www.test.com "
buffer not empty so append "Mar 16, 2013  Fake book entry. " to text
buffer not empty so append "www.book.com " to text
buffer empty or eof so output "Mar 16, 2013  Fake book entry. www.book.com "
buffer not empty so append "Jan 02, 2011  Test title. Frank Testing " to text
buffer not empty so append "www.titlestore.com " to text
buffer empty or eof so output "Jan 02, 2011  Test title. Frank Testing www.titlestore.com "
*/
proc sql;
select * from input_text;
/*
text
--------------------------------------------------------------------------------
Feb 15, 2012. Testing how data is working. www.test.com                         
Mar 16, 2013  Fake book entry. www.book.com                                     
Jan 02, 2011  Test title. Frank Testing www.titlestore.com                      
*/

Solution
‎04-22-2016 08:49 AM
Trusted Advisor
Posts: 1,118

Re: Working with text file with blank rows between fields

Posted in reply to jerry898969

Hello @jerry898969,

 

Would this be any good?

/* Create test data */

data have;
input string $80.;
cards;
Feb 15, 2012. Testing how data is working. www.test.com
. 
Mar 16, 2013  Fake book entry. 
www.book.com
. 
Jan 02, 2011  Test title. Frank Testing
www.titlestore.com
;

/* Concatenate lines that belong together */

data want;
length string $80 text $400; /* please adapt lengths as appropriate */
do until(string=' ' | last);
  set have end=last;
  text=catx(' ', text, string);
end;
drop string;
run;
Super Contributor
Posts: 400

Re: Working with text file with blank rows between fields

Posted in reply to FreelanceReinhard

Thank you both for your replies.

 

FreelanceReinhard,  your solution seems to have worked perfectly.   I'm doing some testing on it now, but it looks good.

 

Thank you

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 461 views
  • 1 like
  • 5 in conversation