DATA Step, Macro, Functions and more

Problem importing a txt-file

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 126
Accepted Solution

Problem importing a txt-file

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;


Accepted Solutions
Solution
‎11-28-2017 05:25 PM
Super User
Super User
Posts: 8,287

Re: Problem importing a txt-file

[ Edited ]
Posted in reply to maroulator

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 ;

 

View solution in original post


All Replies
Super User
Posts: 24,026

Re: Problem importing a txt-file

Posted in reply to maroulator
Have you tried adding the MISSOVER or TRUNCOVER option to your INFILE statement?
Frequent Contributor
Posts: 126

Re: Problem importing a txt-file

I have; my problem persists.

Super User
Posts: 24,026

Re: Problem importing a txt-file

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

Frequent Contributor
Posts: 126

Re: Problem importing a txt-file

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

 

Solution
‎11-28-2017 05:25 PM
Super User
Super User
Posts: 8,287

Re: Problem importing a txt-file

[ Edited ]
Posted in reply to maroulator

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 ;

 

Frequent Contributor
Posts: 126

Re: Problem importing a txt-file

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?

 

 

Super User
Super User
Posts: 8,287

Re: Problem importing a txt-file

Posted in reply to maroulator

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 269 views
  • 1 like
  • 3 in conversation