topic Re: Problem importing a txt-file in SAS Programming

Problem importing a txt-file

maroulator — Tue, 28 Nov 2017 20:49:13 GMT

To whom it may concern,

I have been trying to import the attached .txt file into SAS using the code below. For some reason, I encounter a problem after var26 whereby some of the remaining variables in my input file either get set to blank or do not get picked up at all. Can someone please provide some insight?

Many thanks in advance.

data rates1;
infile "/Pathname/InterestRates.txt" delimiter='09'x;
input var1-var360;
format var1-var360 best32.;
run;

proc print data=rates1;
run;

Re: Problem importing a txt-file

Reeza — Tue, 28 Nov 2017 21:05:36 GMT

Have you tried adding the MISSOVER or TRUNCOVER option to your INFILE statement?

Re: Problem importing a txt-file

maroulator — Tue, 28 Nov 2017 21:14:20 GMT

I have; my problem persists.

Re: Problem importing a txt-file

Reeza — Tue, 28 Nov 2017 21:32:02 GMT

Try setting LRECL to a large number - with 360 variables you might be beyond the default length.

Re: Problem importing a txt-file

maroulator — Tue, 28 Nov 2017 21:44:57 GMT

I tried that earlier; the infile statement in the original version of my code looked like this:

infile '/Pathname/InterestRates.txt' dlm=' ' TRUNCOVER MISSOVER lrecl=32767;

The problem persists

Re: Problem importing a txt-file

Tom — Tue, 28 Nov 2017 22:27:42 GMT

The beginning of the file has spaces between the values and then later it appears to have tabs instead.

You can just tell SAS to use either as the delimiter.

12    data want ;
13      infile 'C:\Downloads\DataSample.txt' dsd dlm='2009'x truncover ;
14      input v1-v360 ;
15    run;

NOTE: The infile 'C:\Downloads\DataSample.txt' is:
      Filename=C:\Downloads\DataSample.txt,
      RECFM=V,LRECL=32767,File Size (bytes)=4275,
      Last Modified=28Nov2017:16:49:50,
      Create Time=28Nov2017:16:49:50

NOTE: 1 record was read from the infile 'C:\Downloads\DataSample.txt'.

Or skip the DSD and DLM options and just use the EXPANDTABS option to convert the tabs into spaces.

infile 'C:\Downloads\DataSample.txt' expandtabs truncover ;

Re: Problem importing a txt-file

maroulator — Tue, 28 Nov 2017 22:27:07 GMT

Hi Tom, thanks for the reply. Just to clarify, how did you notice the two different delimiters in the input file? Just by eye-balling it?

Re: Problem importing a txt-file

Tom — Tue, 28 Nov 2017 22:29:21 GMT

@maroulator wrote:

Hi Tom, thanks for the reply. Just to clarify, how did you notice the two different delimiters in the input file? Just by eye-balling it?

SAS told me. It told me where to look and showed me what was there.

NOTE: Invalid data for v26 in line 1 295-401.
NOTE: Invalid data for v52 in line 1 698-766.
NOTE: Invalid data for v167 in line 1 2126-2158.
NOTE: Invalid data for v168 in line 1 2160-2181.
NOTE: Invalid data for v233 in line 1 2942-2963.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+--
1         1.411895789 1.44330829 1.49552952 1.55391441 1.59009382 1.614501576 1.637530421 1.665363942 1.691
      98  646417 1.715162301 1.736882051 1.757064002 1.775726446 1.79324709 1.809712927 1.824861859 1.83894
     195  1566 1.852194997 1.864628077 1.876424783 1.887605005 1.898214444 1.908246467 1.91791298 1.9274829

     292  53 1.937397302.1.946735819.1.955412166.1.963517714.1.971163054.1.978439641.1.985382543.1.99200438
    ZONE  3323233333333303233333333303233333333303233333333303233333333303233333333303233333333303233333333
    NUMR  5301E93739730291E94673581991E95541216691E96351771491E97116305491E97843964191E98538254391E99200438