Flat File Definition and FIRSTOBS - multiple files at once

[JJ_Murrin]

Hi - 

I started to post a thread regarding the FIRSTOBS not working within DI Studio. Then, I realized that when using an ASTERISK in the flat file definition's filename to pickup multiple files in the same folder, the FIRSTOBS was working; but, as the name certainly implies, it only ignores the first header in all of the data as it is combined. Therefore, the headers in the second thru nth files were kept!

Anyone know of a way to ignore each header in each file ? 

Thank you...

[DI Studio v4.902]

[Reeza]

You can use the END option or EOV option in SAS Base. 

See this post for details, if you were using SAS Base, hopefully some will translate to your work but it may not be valid for DI Studio. 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

[Tom]

You can use the EOV= option, but it is a little tricky.

I have started using the FILENAME= option and LAG() as it is easier.

For both one key is to prime the pump with an input statement with a trailing @.

data want ;
  length filename $200 ;
  infile "&path/*.txt" filename=filename ;
  input @;
  if filename ne lag(filename) then input;
  ....
run;

For the EOV= option you need to reset the flag. Otherwise once it changes to 1 indicating the start of the second file it stays 1. 

data want ;
  infile "&path/*.txt" firstobs=2 eov=eov ;
  eov=0;
  input @;
  if eov then input;
  ....
run;

[Patrick]

@JJ_Murrin

Using DIS to generate code as @Tom proposes you need to amend the External File object by either writing your own INFILE statement ("Override generated INFILE statement....") or by injecting additional code to the generated INFILE statement.

Below shows how to implement the 2nd option:

[JJ_Murrin · Accepted solution]

One other thing that I have done is to use a WHERE clause within the FILE READER transformation. Hopefully, the column header values will not change. After some syntax errors, I determined that this statement:

columnHeader NOT EQ 'literal'

on the "Table Options - Advanced" tab and submenu will not load the headers to the next step within the job.

Thanks all...

J.J. 

[Patrick]

---

@JJ_Murrin wrote:

One other thing that I have done is to use a WHERE clause within the FILE READER transformation. Hopefully, the column header values will not change. After some syntax errors, I determined that this statement:

columnHeader NOT EQ 'literal'

on the "Table Options - Advanced" tab and submenu will not load the headers to the next step within the job.

Thanks all...

J.J. 

---

@JJ_Murrin That doesn't sound right. You need to inspect (and understand) the generated code to determine if settings done in a transformation have the effect you need.