topic Re: Flat File Definition and FIRSTOBS - multiple files at once in SAS Data Management

Flat File Definition and FIRSTOBS - multiple files at once

JJ_Murrin — Tue, 27 Mar 2018 20:23:19 GMT

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]

Re: Flat File Definition and FIRSTOBS - multiple files at once

Reeza — Tue, 27 Mar 2018 21:09:57 GMT

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-text-files-that-have/ta-p/223627

Re: Flat File Definition and FIRSTOBS - multiple files at once

Tom — Wed, 28 Mar 2018 03:32:53 GMT

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;

Re: Flat File Definition and FIRSTOBS - multiple files at once

Patrick — Wed, 28 Mar 2018 02:55:55 GMT

@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:

Re: Flat File Definition and FIRSTOBS - multiple files at once

JJ_Murrin — Mon, 02 Apr 2018 15:22:44 GMT

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.

Re: Flat File Definition and FIRSTOBS - multiple files at once

Patrick — Mon, 02 Apr 2018 21:41:06 GMT

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