topic Re: Another input line to process in SAS Programming

Another input line to process

FollinLane — Tue, 10 May 2016 12:20:10 GMT

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;

Re: Another input line to process

RW9 — Tue, 10 May 2016 12:59:33 GMT

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.

Re: Another input line to process

Ankit_Patel — Tue, 10 May 2016 13:06:41 GMT

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

Thank you.

Re: Another input line to process

Jim_G — Tue, 10 May 2016 13:12:38 GMT

Input the TYPE with a double @@

Execute different inputs based on the record type.

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

Jim

Re: Another input line to process

FollinLane — Tue, 10 May 2016 13:29:31 GMT

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.

Re: Another input line to process

FollinLane — Tue, 10 May 2016 13:30:41 GMT

Ankit,

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

Thank you.

Re: Another input line to process

ballardw — Tue, 10 May 2016 14:24:47 GMT

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;

end;

Else do;

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.

Re: Another input line to process

Astounding — Tue, 10 May 2016 14:37:19 GMT

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/m-p/267877#M52971

Re: Another input line to process

ballardw — Tue, 10 May 2016 14:57:22 GMT

@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/m-p/267877#M52971

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

Re: Another input line to process

FollinLane — Tue, 10 May 2016 15:00:16 GMT

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.

Re: Another input line to process

Astounding — Tue, 10 May 2016 15:18:22 GMT

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

Re: Another input line to process

FollinLane — Tue, 10 May 2016 16:11:49 GMT

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

Re: Another input line to process

Astounding — Tue, 10 May 2016 16:44:14 GMT

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?

Re: Another input line to process

FollinLane — Tue, 10 May 2016 17:04:13 GMT

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.

Re: Another input line to process

Astounding — Tue, 10 May 2016 17:47:55 GMT

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;

Re: Another input line to process

FollinLane — Tue, 10 May 2016 19:18:34 GMT

This seems to have worked. I will need a bit of time to review the results; and then will take time to understand the solution as I'm new to this type of processing. I may have some questions.

Thank you again!