reading text data with mutiple and different number of lines per observation

[wadsworth jonathan]

Dear Statalst

I'm having problems reading in a text file that has multiple lines and different lines per observation. I am aware of the posting from 13/4/2009 which deals with a similar issue and recommends infix as the root to solve the problem but the structure of my data is slightly different.

All my records are at the household level . The data is essentially the interviewers' remarks on the state of the house sampled. We would like to extract and use some of the information contained in these remarks for our subsequent analysis (eg identify whether the house is considered "small" by the interviewer)

Each new record starts with a 6 digit household code
The third "word" on the first line of each household record indicates the subsequent number of lines of text for that household. So in the data subset posted below household 001003 has 1 further line of text
and household 00104 has 2 lines of text

I can extract the 1st line for each household using the commands

infix hhid 1-6 str remark 8-13 recno 19 using "$find2\test.txt" if recno>0 & recno<9999, clear
drop if hhid==.

save "$find2\hhid", replace

and so i have a household id and a variable (recno) which contains the number of subsequent lines of text for that household

But i cannot work out how to read and join the subsequent rows of text to the relevant household

Any help anyone could give on this would be warmly appreciated

Many thanks

Jonathan Wadsworth


Here is a sample of the data


001003 REMI_M 1
Not yet entiteled to pension under state insurance
001004 REMA_M 2
Rent to outside Landlord
See Cards 0329 0831 0832
001005 REMA_M 2
PAID TO OUTSIDE LANDLORD
SEE CARDS 0051 AND 0945 TO 0949
001005 REMI_M 1
op note: Employer of Earner 1 - Builders, originally entered as P Brown.
001006 REMA_M 2
RENT PAID TO OUTSIDE LANDLORD
SEE CARDS 0051 0945 TO 0948 0950
001007 REMA_M 2
RENT TO OUTSIDE LANDLORD
SEE CARD 0915
001008 REMA_M 2
rent: own £24
We assume this means a notional annual rent
001008 REMI_M 2
£150 P.A. is given as earnings. Interviewer has subsequently pencilled in
57/7d for both earnings cols
001009 REMA_M 2
RENT TO OUTSIDE LANDLORD
SEE CARD 0891
001010 REMA_M 2
PAID TO OUTSIDE LANDLORD
SEE CARD 0063
001011 REMA_M 1
SEE CARD 0784
001011 REMI_M 1
MR THOMAS PAYS RENT TO HEAD FOR FURNISHED ROOMS

[daniel klein]

You don't say how you want the data organized. I'm sure there's a clever way of doing this with infix or some other import routine but I'll got straightforward with

clear

generate str6 hhid = ""
generate strL text = ""

tempname fh

file open `fh' using "test.txt" , read

file read `fh' line

while ( !r(eof) ) {
    
    gettoken hhid  line : line
    gettoken trash line : line 
    gettoken recno line : line
    
    local N1 = c(N)+1
    
    quietly set obs `=`c(N)'+`recno''
    forvalues i = `N1'/`=c(N)' {
        
        quietly replace hhid = "`hhid'" in `i'
        
        file read `fh' text
        quietly replace text = `"`text'"' in `i'
        
    }
    
    file read `fh' line
    
}

file close `fh'

Your example dataset (stored in plain text file) reads as

. list , sepby(hhid)

     +------------------------------------------------------------------------------------+
     |   hhid                                                                        text |
     |------------------------------------------------------------------------------------|
  1. | 001003                          Not yet entiteled to pension under state insurance |
     |------------------------------------------------------------------------------------|
  2. | 001004                                                    Rent to outside Landlord |
  3. | 001004                                                    See Cards 0329 0831 0832 |
     |------------------------------------------------------------------------------------|
  4. | 001005                                                    PAID TO OUTSIDE LANDLORD |
  5. | 001005                                             SEE CARDS 0051 AND 0945 TO 0949 |
  6. | 001005    op note: Employer of Earner 1 - Builders, originally entered as P Brown. |
     |------------------------------------------------------------------------------------|
  7. | 001006                                               RENT PAID TO OUTSIDE LANDLORD |
  8. | 001006                                            SEE CARDS 0051 0945 TO 0948 0950 |
     |------------------------------------------------------------------------------------|
  9. | 001007                                                    RENT TO OUTSIDE LANDLORD |
 10. | 001007                                                               SEE CARD 0915 |
     |------------------------------------------------------------------------------------|
 11. | 001008                                                               rent: own £24 |
 12. | 001008                                 We assume this means a notional annual rent |
 13. | 001008   £150 P.A. is given as earnings. Interviewer has subsequently pencilled in |
 14. | 001008                                                57/7d for both earnings cols |
     |------------------------------------------------------------------------------------|
 15. | 001009                                                    RENT TO OUTSIDE LANDLORD |
 16. | 001009                                                               SEE CARD 0891 |
     |------------------------------------------------------------------------------------|
 17. | 001010                                                    PAID TO OUTSIDE LANDLORD |
 18. | 001010                                                               SEE CARD 0063 |
     |------------------------------------------------------------------------------------|
 19. | 001011                                                               SEE CARD 0784 |
 20. | 001011                             MR THOMAS PAYS RENT TO HEAD FOR FURNISHED ROOMS |
     +------------------------------------------------------------------------------------+

[wadsworth jonathan]

Well that worked. We can forget about infix. Thank you so much Daniel