DATA Step, Macro, Functions and more

Another input line to process

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Another input line to process

Per the code below, is there a way to read in another sequential final line to capture data 'Element11' w/out changing its structure? After receiving help on this matter, the code worked great until the data file changed to include the additional line to process.  I've tried to solve this several different ways w/out any luck.

 

Thank you.

 

DATA TEMP(drop=TYPE);                    

  INFILE IN1 end=done;             

      INPUT                              

        @23 ID  $16.                 @66  Element1    $13.;

      INPUT                              

        @31 Element2  $8.      @45  Element3    $10.;               

      INPUT                               

        @25 Element4  $14.   @65  Element5    $12.;  

 

if not done then do;

   input @23 Type $1. @@;

   if type ne 'A' then input 

        @1 Element6  $12.  @25  Element7   $4.  @32  Element8   $4.  @50  Element9    $4.  @63  Element10    $4.;

end;

run;


Accepted Solutions
Solution
‎05-17-2016 07:12 AM
Super User
Posts: 5,497

Re: Another input line to process

Posted in reply to FollinLane

All things considered, I think this is what you are looking for.  You might have to check that the INPUT statements are looking in the right places.  It's convoluted (which often means there's an easier way) and untested ... so we'll see how close it comes.

 

DATA TEMP(drop=TYPE);                    

  INFILE IN1 end=done;             

      INPUT                              

        @23 ID  $16.                 @66  Element1    $13.;

      INPUT                              

        @31 Element2  $8.      @45  Element3    $10.;               

      INPUT                               

        @25 Element4  $14.   @65  Element5    $12.;  

 

if done then output;

input @23 Type $1. @@ ;

if type='A' then do;

   output;

   return;

end;

if type='B' then do;

   input @44 Element11;

   output;

   return;

end;

input  @1 Element6  $12.  @25  Element7   $4.  @32  Element8   $4.  @50  Element9    $4.  @63  Element10    $4.;

if done then output;

input @23 Type $1. @@ ;

if type='B' then  input @44  Element11;

output;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Another input line to process

Posted in reply to FollinLane

Hi,

 

Sorry, can't see any file?  Normally the @@ at the end of a row holds the row over to the next one.

 

More importantly though, why is the file changing?  First thing I would always do - import agreement, signed by vendor and receiver, detailing structure.  Without a set process you may as wel just re-code the whole at each import?  As your the one who will use the data, I would push back to be provided something which is useful to you.

Frequent Contributor
Posts: 95

Re: Another input line to process

Input the TYPE  with a double @@

 

Execute different inputs based on the record type.

 

Use a   /   to get to another record (obs).

 

Jim

Occasional Contributor
Posts: 16

Re: Another input line to process

Hi RW9,

 

The file changed because it is part of a research project looking at historical data.  Over the course of the years we are finding the additional line data had been included after a certain period. For you and others looking at this, I will need to mock-up a data file to attach as the information we are analyzing is sensitive (asap).

 

Thank you.

New Contributor
Posts: 2

Re: Another input line to process

Posted in reply to FollinLane

can please show the raw data file structure to help you out.

Thank you.

Occasional Contributor
Posts: 16

Re: Another input line to process

Posted in reply to Ankit_Patel

Ankit,

 

Please see my response to RW9 concerning the data structure.  I will be sending asap.

 

Thank you.

Super User
Posts: 11,343

Re: Another input line to process

Posted in reply to FollinLane

Does your Type varialbe only occur on every fourth line of the data?

 

If your files have enough changes you may end up have to look at entire lines before deciding where in the file or what role a specific line plays.

Something along the lines of :

 

 INFILE IN1 end=done;

input @;

if substr (_infile_,23,1) = 'A' then do;

   <stuff when type is A>

end;

Else do;

   <stuff when type is not A>

end;

 

The @ holds the current line for input.

 

I've had to use this type of code to find the start of recurring but not identical data within files.

You may also want to look up use of @'character string' or @character-Variable or @(character-expression) for reading data that may occur in different columns following keywords.

Also you may find the Line pointer control # useful with the Infile N= option to read multi-line records easier.

 

Super User
Posts: 5,497

Re: Another input line to process

ballardw,

 

A lot of those questions can be answered by looking at the originally posted problem (before the additional data wrinkle was added).

 

https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-a-new-data-line-via-the-data-step/...

 

Super User
Posts: 11,343

Re: Another input line to process

Posted in reply to Astounding

Astounding wrote:

ballardw,

 

A lot of those questions can be answered by looking at the originally posted problem (before the additional data wrinkle was added).

 

https://communities.sas.com/t5/Base-SAS-Programming/How-to-create-a-new-data-line-via-the-data-step/...

 


I hadn't read into that as it was solved when I first saw it.

My comments are intended along the lines of other tools that may be worth considering/learning when the next change in the file comes up. I've been involved with reading 30+ years of accumulated flat files and had to look for clues or find "reset" points in reading data (site A changed the format but site B didn't , or find the year, or when site C was included with yet a different format).

 

Occasional Contributor
Posts: 16

Re: Another input line to process

Posted in reply to Astounding

I started a new post as the original problem was solved.  For this, please see Astounding's (thanks) mention.

 

The attached mock-up is how the latter files are generally formatted.

 

Please note that line "B1" is the newer wrinkle.  B1 does not always appear but if it does it is always on line 5.

 

Thank you.

Super User
Posts: 5,497

Re: Another input line to process

Posted in reply to FollinLane

I won't be able to open the attachment.  Is there any way you could post 20 lines of data without using an attachment?

Occasional Contributor
Posts: 16

Re: Another input line to process

Posted in reply to Astounding

I tried my best to keep this simple.  I hope it helps.  Thanks.

 

A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                       
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                                                                    
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
B1.........................................rstuv                                                
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                               
A1....................abcdefghijklmnop...........................efghijklmnopq                                                    
A2............................ghijklmn.......mnopqrstuv
A3.......................cdefghijklmnop..........................klmnopqrstuvw                                                    
klmnop...................qrst...abcd..............jklm.........stuv                                     

Super User
Posts: 5,497

Re: Another input line to process

Posted in reply to FollinLane

This is helpful.  Final questions ...

 

It seems like that if there is a "B1" line it is always last (after ELEMENT 11).  Is that correct?

 

For an ELEMENT11, where does it appear?  For example:

 

input @?? ELEMENT11;

 

If there is a "B1" line, where does "B1" appear?  For example:

 

input @23 TYPE $1. @@;

 

Would column 23 be the correct spot to find the "B"?  Could column 23 ever contain "B" on an ELEMENT11 line?

Occasional Contributor
Posts: 16

Re: Another input line to process

Posted in reply to Astounding

B1 is always last but after Element10 (again it may or may not appear). B1 contains ‘data Element 11’.  In the true file, data Element11 appears/starts  @44.  To identify B1... it is a long label starting in column 1.  Its literal value is:

 

‘Points Expiring By Next Billing Statement: 0’

 

I hope I’m being clear as it must be hard not dealing with the actual data. Thank you.

Solution
‎05-17-2016 07:12 AM
Super User
Posts: 5,497

Re: Another input line to process

Posted in reply to FollinLane

All things considered, I think this is what you are looking for.  You might have to check that the INPUT statements are looking in the right places.  It's convoluted (which often means there's an easier way) and untested ... so we'll see how close it comes.

 

DATA TEMP(drop=TYPE);                    

  INFILE IN1 end=done;             

      INPUT                              

        @23 ID  $16.                 @66  Element1    $13.;

      INPUT                              

        @31 Element2  $8.      @45  Element3    $10.;               

      INPUT                               

        @25 Element4  $14.   @65  Element5    $12.;  

 

if done then output;

input @23 Type $1. @@ ;

if type='A' then do;

   output;

   return;

end;

if type='B' then do;

   input @44 Element11;

   output;

   return;

end;

input  @1 Element6  $12.  @25  Element7   $4.  @32  Element8   $4.  @50  Element9    $4.  @63  Element10    $4.;

if done then output;

input @23 Type $1. @@ ;

if type='B' then  input @44  Element11;

output;

run;

☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 531 views
  • 0 likes
  • 6 in conversation