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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

15 REPLIES 15
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Jim_G
Pyrite | Level 9

Input the TYPE  with a double @@

 

Execute different inputs based on the record type.

 

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

 

Jim

FollinLane
Obsidian | Level 7

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.

Ankit_Patel
Calcite | Level 5

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

Thank you.

FollinLane
Obsidian | Level 7

Ankit,

 

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

 

Thank you.

ballardw
Super User

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.

 

Astounding
PROC Star

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/...

 

ballardw
Super User

@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).

 

FollinLane
Obsidian | Level 7

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.

Astounding
PROC Star

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

FollinLane
Obsidian | Level 7

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                                     

Astounding
PROC Star

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?

FollinLane
Obsidian | Level 7

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.

Astounding
PROC Star

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 15 replies
  • 1836 views
  • 0 likes
  • 6 in conversation