topic Re: Using infile to import multi row record from text file in SAS Procedures

Using infile to import multi row record from text file

jerry898969 — Mon, 16 Mar 2015 15:01:32 GMT

Hello,

I have a text file I have to read in and in the past it's all been one row per record. This time I have a multi-line per record and I'm not sure the best way to approach importing the text file into a sas table.

Example Data

ID:FNAME:MNAME:LNAME:CITY:STATE:ZIP:PHONE:PREV_CITY:PREV_STATE:PREV_ZIP:PREV_PHONE

1:Homer:Jay:Simpson

:Springfield:::(222)-333-4444

:Langley Falls:VA:88888:(555)-666-7777

2:Bill:Phil:Williams

:New York:NY::(123)-456-7890

::::(987)-000-1212

How can I change my previous code to bring in the multiple lines?

filename temp "c:\temp\temp.txt" ;

data temp ;

infile temp dlm=':', mu dsd lrecl=260 missover pad firstobs = 2 ;

length

ID 8

FNAME $20

MNAME $20

LNAME $30

CITY $40

STATE $2

ZIP $5

PHONE $15

PREV_CITY $40

PREV_STATE $2

PREV_ZIP $5

PREV_PHONE $15

;

input

FNAME $

MNAME $

LNAME $

CITY $

STATE $

ZIP $

PHONE $

PREV_CITY $

PREV_STATE $

PREV_ZIP $

PREV_PHONE $

;

run ;

Thank you

Re: Using infile to import multi row record from text file

ballardw — Mon, 16 Mar 2015 15:13:52 GMT

First question:

Is there always exactly 3 rows per record? If so then there's a relatively easy solution using N= option on the INFILE definition to allow access to the 3 lines.

input #1 id

fname

mname

lname

#2 city

state

zip

phone

#3 Prev_city (etc)

;

If the number varies then more work;

Re: Using infile to import multi row record from text file

jerry898969 — Mon, 16 Mar 2015 16:16:26 GMT

Thank you ballardw for the reply.

Each record will always have the same number of rows. Right now I'm at 3 but it might go to 4 but it will be consistent across each record.

That did it.

Thank you

Re: Using infile to import multi row record from text file

Tom — Mon, 16 Mar 2015 16:40:43 GMT

Your example data will work well with FLOWOVER option. Just as long as each observation has the right number of fields it won't matter which line they are on.

data temp ;

infile cards dlm=':' dsd FLOWOVER firstobs = 2 ;

length

ID 8

FNAME $20

MNAME $20

LNAME $30

CITY $40

STATE $ 2

ZIP $ 5

PHONE $15

PREV_CITY $40

PREV_STATE $ 2

PREV_ZIP $ 5

PREV_PHONE $15

;

input id -- PREV_PHONE ;

cards4;

ID:FNAME:MNAME:LNAME:CITY:STATE:ZIP:PHONE:PREV_CITY:PREV_STATE:PREV_ZIP:PREV_PHONE

1:Homer:Jay:Simpson

:Springfield:::(222)-333-4444

:Langley Falls:VA:88888:(555)-666-7777

2:Bill:Phil:Williams

:New York:NY::(123)-456-7890

::::(987)-000-1212

;;;;

Re: Using infile to import multi row record from text file

data_null__ — Mon, 16 Mar 2015 16:43:00 GMT

You can use the little known LOSTCARD statement to help you insure that the number of lines is correct.

filename FT15F001 temp; 
data temp;      
    infile FT15F001 dlm=':' dsd lrecl=260 missover pad firstobs = 2;      
    length           
        ID           $8  
        FNAME        $20  
        MNAME         $20  
        LNAME        $30          
        CITY        $40  
        STATE       $2  
        ZIP            $5  
        PHONE       $15  
        PREV_CITY    $40  
        PREV_STATE  $2  
        PREV_ZIP    $5  
        PREV_PHONE  $15  
    ;  
   input #1 ID  FNAME MNAME LNAME 
         #2 id2 CITY STATE ZIP PHONE
         #3 id3 PREV_CITY PREV_STATE PREV_ZIP PREV_PHONE
      ;
   if missing(id) or cmiss(id2,id3) ne 2 then lostcard; 
parmcards4; 
ID:FNAME:MNAME:LNAME:CITY:STATE:ZIP:PHONE:PREV_CITY:PREV_STATE:PREV_ZIP:PREV_PHONE
1:Homer:Jay:Simpson
:Springfield:::(222)-333-4444
:Langley Falls:VA:88888555)-666-7777
:Springfield:::(222)-333-4444
2:Bill:Phil:Williams
:New York:NY::(123)-456-7890
::::(987)-000-1212
2:New York:NY::(123)-456-7890
3:Wildbill:Phil:Williams
:New York:NY::(123)-456-7890
::::(987)-000-1212
;;;;
   run; 
proc print; 
   run;