BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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          

        ID         

        FNAME        $   

        MNAME         $

        LNAME        $

        CITY        $

        STATE       $

        ZIP            $

        PHONE       $

        PREV_CITY    $

        PREV_STATE  $

        PREV_ZIP    $

        PREV_PHONE  $

    ;

run ;

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

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;

jerry898969
Pyrite | Level 9

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

data_null__
Jade | Level 19

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:
88888Smiley Sad555)-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;

3-16-2015 11-36-18 AM.png3-16-2015 11-38-37 AM.png
Tom
Super User Tom
Super User

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

;;;;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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