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

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

Buddu — Wed, 24 Jun 2015 17:59:36 GMT

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

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

ChrisHemedinger — Mon, 29 Jun 2015 16:20:30 GMT

Moved this into for more attention by the experts.

Chris

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

Patrick — Tue, 30 Jun 2015 10:11:04 GMT

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"

This will generate the "infile" statement as follows:

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

Does this solve your problem?

Thanks,

Patrick

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

LinusH — Tue, 30 Jun 2015 10:22:34 GMT

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

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

Buddu — Sun, 12 Jul 2015 22:05:28 GMT

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

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,

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

LinusH — Mon, 13 Jul 2015 04:06:16 GMT

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

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

Patrick — Mon, 13 Jul 2015 07:53:32 GMT

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

end=last;

input @;

if _n_=1 or last then return;