Help using Base SAS procedures

How do I import a very large text file?

Reply
Occasional Contributor
Posts: 18

How do I import a very large text file?

I'm using SAS 9.4. I'm tring to read in a text file with about 65,000 variables (the first row contains the variable names). This is my code:

 

PROC IMPORT OUT= WORK.full_resolution
DATAFILE= "O:\File Path\Dataset - jdx_dataset.txt"
DBMS=TAB REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

 

When I run this code, I get a message in my log: "Number of names found is less than number of variables found." It then begins reading in the file. It looks like it reads in the first 4,000 or so variables correctly, but then everything looks missing after that. Does anyone have any advice on how to read in the full file correctly?

Super User
Posts: 17,958

Re: How do I import a very large text file?

Do you have a file layout document that details the fields? 

Is the file wide and big, ie many rows? 

Respected Advisor
Posts: 3,777

Re: How do I import a very large text file?

[ Edited ]

I'm guessing your LRECL is GT 32767.  I'm not sure if PROC IMPORT will read records longer.

 

Update I tested is using SAS 9.4 UNIX os and found that using an FILENAME statement to point to the fill will allow PROC IMPORT to use an LRECL gt 32767.

 

filename FT43F001 'O:\File Path\Dataset - jdx_dataset.txt' lrecl=64000; *or larger as needed.;
proc import datafile=FT43F001  dbms=tab out=test replace;
   run;

 

Super User
Super User
Posts: 6,502

Re: How do I import a very large text file?

What version of 9.4?  I tried it with 9.4M3 (SYSVLONG4 9.04.01M3P06242015) and it still truncated the variable names at 32K.

So in code below it can only find names for the first 992 variables.

 

filename csv '~/test/long.csv' lrecl=1048575 ;

data _null_;
  file csv dsd ;
  do i=1 to 1000 ;
     name='name_6789012345678901234567_'||put(i,z4.);
     put name @;
  end;
  put;
  do i=1 to 2000;
   put i @;
  end;
  put;
run;
proc import datafile=csv out=test1 replace
  dbms=dlm
;
 delimiter=',';
run;
Respected Advisor
Posts: 3,777

Re: How do I import a very large text file?

Yes, I see I was going on the fact that the generated code no longer has LRECL=32767 hard coded into the INFILE statement.

 

 infile CSV delimiter = ',' MISSOVER DSD  firstobs=2 ;

That LRECL bug I think was introduced with SAS 9.2.  They half way fixed it reckon.

 

I don't see why PROC IMPORT would stop creating names at that point.  I can do it with a data step proc transpose. 

 

Never trust a program with a parameter called guessing-rows.

Trusted Advisor
Posts: 1,115

Re: How do I import a very large text file?

The latest PROC IMPORT documentation says: "The maximum LRECL that the IMPORT procedure supports is 32767."

Super User
Super User
Posts: 6,502

Re: How do I import a very large text file?

[ Edited ]

65,000 variables is way too many for PROC IMPORT to handle.  I cannot handle text lines longer than 32,767 characters.

Do you know what is in the file?  If so then just write the data step to read it yourself. Why force SAS to guess what is in your file?

data want ;
  infile 'myfile' dsd dlm='09'x firstobs=2 truncover ;
  input var1-var65000 ; 
run; 

 

Occasional Contributor
Posts: 18

How do I import a very large text file?

I'm using SAS 9.4. I'm tring to read in a text file with about 65,000 variables (the first row contains the variable names). This is my code:

 

PROC IMPORT OUT= WORK.full_resolution 
DATAFILE= "O:\File Path\Dataset - jdx_dataset.txt" 
DBMS=TAB REPLACE;
GETNAMES=YES;
DATAROW=2; 
RUN;

 

When I run this code, I get a message in my log: "Number of names found is less than number of variables found." It then begins reading in the file. It looks like it reads in the first 4,000 or so variables correctly, but then everything looks missing after that. Does anyone have any advice on how to read in the full file correctly?

Super User
Posts: 10,550

Re: How do I import a very large text file?

First issue: 65000 variables, even at one character per variable is likely exceeding the LREC (line length) limit.

You can try setting a larger length using

 

options lrecl=32M; /* sets input line length to 32,000,000 characters look up LRECL for options for other ways to provide large values.*/

 

I'm not sure that Proc Import really wants to handle 65000 variables though. The message "Number of names found is less than number of variables found." could mean that you do not have a name for some variables. In this case SAS generally will create variable names like VarXXXXX.

Trusted Advisor
Posts: 1,115

Re: How do I import a very large text file?

If the variable names are longer than (most of) the values, it seems quite plausible to me that, after the truncation to the default record length, SAS will find fewer variable names than values. So, increasing LRECL might already solve this specific problem (but there will likely be more with that many variables).

Super User
Super User
Posts: 6,502

Re: How do I import a very large text file?

[ Edited ]

Read the data yourself and do your own analysis of what variable types to use for each column.  You can then generate you own data step to read it.  I am sure the data step will look much nicer than the stuff that PROC IMPORT generates.

 

data names (keep=col name )
     cells (drop=name)
;
  infile csv dsd column=cc length=ll truncover ;
  if _n_=1 then do ;
    input @;
    do col=1 by 1 while (cc <= ll) ;
      length name $32 ;
      input name @;
      output names;
    end;
    input;
  end;
  row+1;
  input @;
  do col=1 by 1 while (cc <= ll) ;
    length string $200 ;
    input string @;
    length = lengthn(string);
    empty = missing(string);
    not_number=missing(input(string,??comma32.));
    not_date  =missing(input(string,??anydtdte.));
    not_time  =missing(input(string,??anydttme.));
    not_dtime =missing(input(string,??anydtdtm.));
    output cells;
  end;
run;

proc summary data=cells nway ;
 class col ;
 var empty not_: ;
 output out=types(drop=_type_) max(length)= sum(empty not_:)= ;
run;

data vars;
  merge names types ;
  by col;
run;

 

Occasional Contributor
Posts: 5

Re: How do I import a very large text file?

Fabulous tip, Tom!  Smiley Very Happy

Ask a Question
Discussion stats
  • 11 replies
  • 1203 views
  • 2 likes
  • 7 in conversation