BookmarkSubscribeRSS Feed
Buddu
Calcite | Level 5

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

6 REPLIES 6
ChrisHemedinger
Community Manager

Moved this into for more attention by the experts.


Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Patrick
Opal | Level 21

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
Buddu
Calcite | Level 5

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,

LinusH
Tourmaline | Level 20

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

Data never sleeps
Patrick
Opal | Level 21

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

end=last;

input @;

if _n_=1 or last then return;

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
  • 6 replies
  • 1905 views
  • 2 likes
  • 4 in conversation