How do I import a very large text file?

[acuffza]

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?

[acuffza]

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?

[ballardw]

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.

[Reeza]

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

Is the file wide and big, ie many rows? 

[data_null__]

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;

[Tom]

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;

[data_null__]

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.

[FreelanceReinh]

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

[Tom]

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; 

[FreelanceReinh]

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).

[Tom]

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;

[KenMac]

Fabulous tip, Tom!