Extract data from a unfriendly text file

[ballardw]

---

@HB wrote:

can mess around with the read  instructions and read only the lines you want or just read everything and drop the variables you don't want.  You can also read things properly as dates and numbers and not generic character variables of length 20 as I have.

Maybe like

@#6 @5 id & 7.
@#7 @16 dob:mmddyy10.

---

@one of the other options for the read at is to read at a text value such as: #6 @'Student:'  and you don't even have to count the characters. I have to say that I found the mixture of some sort of row header, such as Student, ID, Date of Birth and three rows without them odd.

One concern is the first example file has varying numbers of blank lines after Description so there may need to be some code to resync between records if the actual data has that behavior. There may also have to be something to deal with a description that runs to two or more lines but @Wei2017 hasn't specified if that happens.

[Tom]

---

@HB wrote:

EDIT:  I don't know why this answer formatted this way, with no breaks in the SAS code and a scroll on the last piece of text. 

---

Do not edit the text in the code block in the normal editor. Instead place the cursor in the block and click the menu bar icon and edit the code block in the pop-up window.  If you edit it directly in the main editor window it loses the special formatting.

Add the TRUNCOVER option to your INFILE statement and then you don't need those &'s and :'s in the INPUT statement.

[HB]

Do not edit the text in the code block in the normal editor. Instead place the cursor in the block and click the menu bar icon and edit the code block in the pop-up window.  If you edit it directly in the main editor window it loses the special formatting.

Add the TRUNCOVER option to your INFILE statement and then you don't need those &'s and :'s in the INPUT statement.

Thanks!! Cool!!

[Wei2017]

Thank you. The original text file is far more complex than that i provided. You code gives me a hint to deal with that unfriendly text file.

[Tom]

Your file looks pretty tame. I am worried because it does not appear to have the same number of lines for each "record".  Does that mean that also does not always have every type of line?  If it is regular then just read the fields directly.  You can use # to / in your INPUT statement (or just use multiple INPUT statements).  So if every record had exactly 14 lines you could do this:

infile 'myfile.txt' n=14 truncover ;
input 
#3 course $30.
#5 @':' student $30.
#6 @':' id 
#7 @':' dob mmddyy12. 
#8 @':' grade
#9 @':' repdate  mmddyy12.
;

Now if the randomness is just in the number of blank lines between groups then you could instead add a little more logic an flexibility by looking for some text that signals the start of a new "record".

infile 'myfile.txt' truncover ;
input @;
if _infile_ = 'Math Exam' then do ;
input 
// course $30.
// @':' student $30.
 / @':' id 
 / @':' dob mmddyy12. 
 / @':' grade
 / @':' repdate  mmddyy12.
;
output;
end;

If it is really messy then you need to read each record type separately and retain the values and execute the OUTPUT statement when you get to the end of the "record" .  Or perhaps when you start another record or hit the end of the file.

Try something like one of the above on your real file and report back.  (Note make sure to add the missing statements to make a complete data step and don't forget to add FORMAT statements for your date variables).

[Wei2017]

Thanks.