Help using Base SAS procedures

Using infile to import multi row record from text file

Accepted Solution Solved
Reply
Super Contributor
Posts: 400
Accepted Solution

Using infile to import multi row record from text file

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:CITYSmiley FrustratedTATE:ZIPSmiley TongueHONESmiley TongueREV_CITYSmiley TongueREV_STATESmiley TongueREV_ZIPSmiley TongueREV_PHONE

1:Homer:JaySmiley Frustratedimpson

Smiley Frustratedpringfield::Smiley Sad222)-333-4444

:Langley Falls:VA:88888Smiley Sad555)-666-7777

2:BillSmiley Tonguehil:Williams

:New York:NY:Smiley Sad123)-456-7890

:::Smiley Sad987)-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          

        ID         

        FNAME        $   

        MNAME         $

        LNAME        $

        CITY        $

        STATE       $

        ZIP            $

        PHONE       $

        PREV_CITY    $

        PREV_STATE  $

        PREV_ZIP    $

        PREV_PHONE  $

    ;

run ;

Thank you


Accepted Solutions
Solution
‎03-16-2015 11:13 AM
Super User
Posts: 11,343

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

Posted in reply to jerry898969

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;

View solution in original post


All Replies
Solution
‎03-16-2015 11:13 AM
Super User
Posts: 11,343

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

Posted in reply to jerry898969

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;

Super Contributor
Posts: 400

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

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

Respected Advisor
Posts: 3,799

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

Posted in reply to jerry898969

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:CITYSmiley FrustratedTATE:ZIPSmiley TongueHONESmiley TongueREV_CITYSmiley TongueREV_STATESmiley TongueREV_ZIP:
PREV_PHONE
1:Homer:JaySmiley Frustratedimpson
Smiley Frustratedpringfield::Smiley Sad
222)-333-4444
:Langley Falls:VA:
88888Smiley Sad555)-666-7777
Smiley Frustratedpringfield::Smiley Sad
222)-333-4444
2:BillSmiley Tonguehil:Williams
:New York:NY:Smiley Sad
123)-456-7890
:::Smiley Sad
987)-000-1212
2:New York:NY:Smiley Sad123)-456-7890
3:WildbillSmiley Tonguehil:Williams
:New York:NY:Smiley Sad
123)-456-7890
:::Smiley Sad
987)-000-1212
;;;;
   run;
proc print;
  
run;

3-16-2015 11-36-18 AM.png3-16-2015 11-38-37 AM.png
Super User
Super User
Posts: 7,076

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

Posted in reply to jerry898969

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:CITYSmiley FrustratedTATE:ZIPSmiley TongueHONESmiley TongueREV_CITYSmiley TongueREV_STATESmiley TongueREV_ZIPSmiley TongueREV_PHONE

1:Homer:JaySmiley Frustratedimpson

Smiley Frustratedpringfield::Smiley Sad222)-333-4444

:Langley Falls:VA:88888Smiley Sad555)-666-7777

2:BillSmiley Tonguehil:Williams

:New York:NY:Smiley Sad123)-456-7890

:::Smiley Sad987)-000-1212

;;;;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 228 views
  • 0 likes
  • 4 in conversation