SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Flat File Definition and FIRSTOBS - multiple files at once

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Flat File Definition and FIRSTOBS - multiple files at once

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]


Accepted Solutions
Solution
‎04-02-2018 11:23 AM
New Contributor
Posts: 3

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

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. 

View solution in original post


All Replies
Super User
Posts: 23,357

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

Posted in reply to JJ_Murrin

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

Super User
Super User
Posts: 7,944

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

[ Edited ]
Posted in reply to JJ_Murrin

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;

  

Respected Advisor
Posts: 4,698

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

Posted in reply to JJ_Murrin

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

Capture.JPG

 

 

Solution
‎04-02-2018 11:23 AM
New Contributor
Posts: 3

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

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. 

Respected Advisor
Posts: 4,698

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

Posted in reply to JJ_Murrin

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

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 203 views
  • 0 likes
  • 4 in conversation