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

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]

1 ACCEPTED SOLUTION

Accepted Solutions
JJ_Murrin
Fluorite | Level 6

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

5 REPLIES 5
Reeza
Super User

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
Super User Tom
Super User

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
Opal | Level 21

@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

 

 

JJ_Murrin
Fluorite | Level 6

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
Opal | Level 21

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

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1632 views
  • 1 like
  • 4 in conversation