Using more than one Input statement to combine data lines

[serge68]

Hi - My input potentially has multiple lines that make up the data line. I'm trying to read those and format into a single output line without much success.

The lines starting with JOB= would be associated with the preceding line. There may be no JOB= lines, or there may be multiple lines.

data have;                                                         
   infile cards missover;                                          
   input #01 name         $ 01-08 @;                               
   if substr(name, 1, 4) ne 'JOB=' then do;                        
   input #02 lastrun      $ 63-66                                  
   else do;                                                        
   input #02 rqmt         $ 05-12                                  
             leadtm       $ 58-59 ;                                
    end;                                                           
   cards;                                                          
FREDFRED 004 FREDFRED FMS      001 ALL  *NONE*  YES   000 000 1732 
BARNEY   004 BARNEY   ABZ      001 ALL  *NONE*  YES   000 000 1341 
JOB=JOB00001        SCHID=000   VRSN=09063/1602   LEADTM=08        
JOB=JOB00043        SCHID=000   VRSN=94322/1613                    
WILMA    004 WILMA    TRE      001 ALL  *NONE*  YES   000 000 0000 
JOB=STEP9999        SCHID=000   VRSN=07263/0650   LEADTM=99        
BETTY    004 BETTY    KIL      001 ALL  *NONE*  YES   000 000 2351 
;           
   run;     
            
proc print; 
   run;     

This is what I would like to see: 

Name		lastrun		rqmt		leadtm	
FREDFRED	1732	
BARNEY	        1341		JOB0001 	08
BARNEY	        1341		JOB00043	
WILMA		0000		STEP9999	99
BETTY		2351

I can't seem to figure out how to handle those entries consisting of multiple lines.

Appreciate any assistance. Thanks. 

[Tom · Accepted solution]

It would be easier to read from a file, since you can use the END= option of the INFILE statement.

So let's convert your example lines into a file first so we have something to program against.

options parmcards=text;
filename text temp;

parmcards;                                                          
FREDFRED 004 FREDFRED FMS      001 ALL  *NONE*  YES   000 000 1732 
BARNEY   004 BARNEY   ABZ      001 ALL  *NONE*  YES   000 000 1341 
JOB=JOB00001        SCHID=000   VRSN=09063/1602   LEADTM=08        
JOB=JOB00043        SCHID=000   VRSN=94322/1613                    
WILMA    004 WILMA    TRE      001 ALL  *NONE*  YES   000 000 0000 
JOB=STEP9999        SCHID=000   VRSN=07263/0650   LEADTM=99        
BETTY    004 BETTY    KIL      001 ALL  *NONE*  YES   000 000 2351 
;  

Now you can read ahead using trailing @@ on the INPUT statement.  Keep track of how many JOB= lines you have seen so you know whether you need to execute and extra OUTPUT statement.

You probably do NOT want the behavior of the MISSOVER option, instead use TRUNCOVER so it doesn't ignore short values at the end of lines.  And to read those JOB= lines you could use @ 'string' pointer control.

data have;                                                         
   infile text truncover end=eof;  
   input name $ 1-8 lastrun $ 63-66;
   do n=0 by 1 while(not eof);
     input @@;
     if _infile_ =: 'JOB=' then do;
       if n then output;
       input @'JOB=' rqmt :$8. @'LEADTM=' leadtm :$2. ;
     end;
     else leave;
   end;
   output;
run;

Or if you make the variable names match the strings in the lines you can used NAMED input style.

data have;                                                         
   infile text truncover end=eof;  
   input name $ 1-8 lastrun $ 63-66;
   do n=0 by 1 while(not eof);
     input @@;
     if _infile_ =: 'JOB=' then do;
       if n then output;
       input job= :$8. leadtm= :$2. ;
     end;
     else leave;
   end;
   output;
run;

Results

Notice that using the named input caused the LEADTM value to be remembered for the second JOB= line.

To fix the issue that named input does not force variables to missing when not found you could add code to clear the value before the input.  But make sure to set the variable's type and length before hand.

data have;                                                         
   infile text truncover end=eof;  
   input name $ 1-8 lastrun $ 63-66;
   do n=0 by 1 while(not eof);
     input @@;
     if _infile_ =: 'JOB=' then do;
       if n then output;
       length job $8 leadtm $2 ;
       call missing(job,leadtm);
       input job= leadtm= ;
     end;
     else leave;
   end;
   output;
run;

[Tom]

You could also just generate two datasets and then merge them together.

Create a unique id to perform the merge with, let's call it ID.

To make the named input happy define variables for all of the named fields on the JOB= lines.

data main jobs(drop=name lastrun);
   infile cards truncover ;
   input @;
   if _infile_ ^=: 'JOB=' then do;
     id+1;
     n=0;
     input name $ 1-8 lastrun $ 63-66;
     output main;
   end;
   else do;
     n+1;
     length job $8 schid $3 vrsn $10 leadtm $2;
     input (job--leadtm) (=)  ;
     output jobs;
   end;
cards;                                                          
FREDFRED 004 FREDFRED FMS      001 ALL  *NONE*  YES   000 000 1732 
BARNEY   004 BARNEY   ABZ      001 ALL  *NONE*  YES   000 000 1341 
JOB=JOB00001        SCHID=000   VRSN=09063/1602   LEADTM=08        
JOB=JOB00043        SCHID=000   VRSN=94322/1613                    
WILMA    004 WILMA    TRE      001 ALL  *NONE*  YES   000 000 0000 
JOB=STEP9999        SCHID=000   VRSN=07263/0650   LEADTM=99        
BETTY    004 BETTY    KIL      001 ALL  *NONE*  YES   000 000 2351 
;

data want;
  merge main jobs;
  by id;
run;

Result

[Ksharp]

data have;                                                         
infile cards truncover ;
length a1-a11 job schid vrsn LEADTM $ 80;
retain a1-a11;
input @;
if _infile_ =: 'JOB=' then  input job= schid= vrsn= LEADTM= ;
 else do;input a1-a11;id+1;end;
cards;                                                          
FREDFRED 004 FREDFRED FMS      001 ALL  *NONE*  YES   000 000 1732 
BARNEY   004 BARNEY   ABZ      001 ALL  *NONE*  YES   000 000 1341 
JOB=JOB00001        SCHID=000   VRSN=09063/1602   LEADTM=08        
JOB=JOB00043        SCHID=000   VRSN=94322/1613                    
WILMA    004 WILMA    TRE      001 ALL  *NONE*  YES   000 000 0000 
JOB=STEP9999        SCHID=000   VRSN=07263/0650   LEADTM=99        
BETTY    004 BETTY    KIL      001 ALL  *NONE*  YES   000 000 2351 
;           

data want(rename=(a1=name a11=lastrun job=rqmt ));
 set have(keep=id a1 a11 job leadtm);
 by id;
 if first.id and last.id then output;
  else do;
   if not first.id then output;
  end;
run;