topic Re: Working with text file with blank rows between fields in SAS Programming

Working with text file with blank rows between fields

jerry898969 — Thu, 21 Apr 2016 16:26:58 GMT

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

Re: Working with text file with blank rows between fields

Reeza — Thu, 21 Apr 2016 16:58:53 GMT

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;

Re: Working with text file with blank rows between fields

ballardw — Thu, 21 Apr 2016 17:31:11 GMT

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

Re: Working with text file with blank rows between fields

jerry898969 — Thu, 21 Apr 2016 17:55:34 GMT

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.

Re: Working with text file with blank rows between fields

Reeza — Thu, 21 Apr 2016 17:58:50 GMT

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

Re: Working with text file with blank rows between fields

jerry898969 — Thu, 21 Apr 2016 18:12:34 GMT

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

Re: Working with text file with blank rows between fields

Pamela_JSRCC — Thu, 21 Apr 2016 22:50:51 GMT

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
*/

Re: Working with text file with blank rows between fields

FreelanceReinh — Thu, 21 Apr 2016 22:51:39 GMT

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;

Re: Working with text file with blank rows between fields

jerry898969 — Fri, 22 Apr 2016 12:48:52 GMT

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