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

How to get rid of the last record while reading a file in SAS DI studio

Reply
New Contributor
Posts: 2

How to get rid of the last record while reading a file in SAS DI studio

Hi SAS,

I wish to know what is the procedure and the transformation to be used to get rid of the last record. Suppose, there is a dummy record in the last observation and I want to get rid of it using a transformation.

Ex: out of 11 recrods, the last is found to be dummy and I want to read only first 10 records..(using transformation and no user written code)Di

Community Manager
Posts: 2,768

Re: How to get rid of the last record while reading a file in SAS DI studio

Moved this into for more attention by the experts.


Chris

Respected Advisor
Posts: 3,899

Re: How to get rid of the last record while reading a file in SAS DI studio

Assuming we're talking about external data (eg. a text file) and not a table.

First question: What constitutes a dummy record? Let's assume this is a record with no data (just an empty line). So writing SAS code you could code something like:

data want;

  infile blah;

  input @;

  if missing(_infile_) then return;

  input a b c;

run;

 

You can "force" DI to generate such code by "injecting" it using the External File Metadata Definition under "File Parameter / Advanced"

Capture.PNG

This will generate the "infile" statement as follows:

Capture.PNG

So basically above approach allows you to "inject" any code between the generated INFILE and INPUT statement.

Does this solve your problem?

Thanks,

Patrick

Super User
Posts: 5,260

Re: How to get rid of the last record while reading a file in SAS DI studio

Neat way to insert user written code in the external file , never thought of this Smiley Happy

Data never sleeps
New Contributor
Posts: 2

Re: How to get rid of the last record while reading a file in SAS DI studio

Hi Patrick,

This did help and yes the file is a .txt. However, dummy means that it has data but cannot be processed further, the entire records needs to be dropped.

After I read the file successfully using the file reader, I make use of slitter to hard code the value present in the variable in row selection

sasq.JPG

I wish to know is there a workaround to strip first and last records. Just like first.variable and last.variable in datastep. The variable here is Mortgage... and if I wish to strip the first header record - I make use of _N_ ~= 1, similarly, I wish to know how to strip the last record of the file rather than hard coding to value or like operator.

Thanks,

Super User
Posts: 5,260

Re: How to get rid of the last record while reading a file in SAS DI studio

Uaing end= infile option instead and use that for the if statement. Untested...

Data never sleeps
Respected Advisor
Posts: 3,899

Re: How to get rid of the last record while reading a file in SAS DI studio

Same as suggests: Add to the infile options in the external metadata something like:

end=last;

input @;

if _n_=1 or last then return;

Ask a Question
Discussion stats
  • 6 replies
  • 614 views
  • 2 likes
  • 4 in conversation