topic Re: Importing a very large .txt file in SAS Enterprise Guide

Importing a very large .txt file

katiauk — Tue, 29 Dec 2015 13:51:50 GMT

Hello,

I am a new user working with SAS Enterprise Guide and I have been trying to import a very large .txt file, with over 10 million rows and around 18 columns.

I have tried the wizard, which does not bring all the records, so I tried the proc import:

PROC IMPORT
   DATAFILE='\\10.42.16.29\gc$\Full_0151108.txt'
   OUT = gc.20151108TESTE;
   DBMS = DLM replace;
   DELIMITER = '|';
   getnames=yes;
run;

The code imports fewer lines than the wizard. I can't seem to find a way to import all of the rows! Any help appreciated.

Thanks.

Re: Importing a very large .txt file

ad123123 — Tue, 29 Dec 2015 14:49:48 GMT

Hi,

Could you try following code:-

data work.temp ;
infile '<file_path>\abc.txt'
lrecl = 32767 <or whatever is your file length>
delimiter = '|'
dsd
missover
firstobs = 1 /*Give this value as 1 if your text file does not contain column name. If your file contains column name this value will be 2*/;
;
attrib <col1> length = $32;
attrib <col2> length = $8;
attrib <col3> length = $26;

/*This will continue till col 16*/

input <col1> <col2> <col3>......<col16>;

run;

Re: Importing a very large .txt file

katiauk — Tue, 29 Dec 2015 15:20:48 GMT

Thanks, I have tried, but only imports the same as my code, 26357 records...

Re: Importing a very large .txt file

Reeza — Tue, 29 Dec 2015 19:57:37 GMT

When you use proc import you'll get some code in the log. Take that code and modify it to read your full file. Most likely you'll need to change the LRECL option because your lines are longer than SAS expects.

Re: Importing a very large .txt file

PGStats — Tue, 29 Dec 2015 21:27:24 GMT

Try adding option IGNOREDOSEOF to your infile statement.

Re: Importing a very large .txt file

Ksharp — Wed, 30 Dec 2015 01:25:47 GMT

What did your SAS LOG display ? Is there any WARNING or ERROR message? I also notice OUT = gc.20151108TESTE; in your code. It is not right. SAS table name can't be start with a digit .

Re: Importing a very large .txt file

Reeza — Wed, 30 Dec 2015 02:04:08 GMT

@Ksharp wrote:
What did your SAS LOG display ? Is there any WARNING or ERROR message? I also notice OUT = gc.20151108TESTE; in your code. It is not right. SAS table name can't be start with a digit .

This was posted in SAS EG forum, and unfortunately SAS EG sets the option validvarname=any by default.

Re: Importing a very large .txt file

Ksharp — Wed, 30 Dec 2015 07:06:21 GMT

I see. But there are must have something like NOTE: WARNING: in LOG. That could help us to know where is the problem, if OP think importing result is not right.

Re: Importing a very large .txt file

ad123123 — Wed, 30 Dec 2015 07:28:03 GMT

Hi,

Can you please paste few rows after 26,357? Also, while running the code did you find any erroneous statement in log?

Re: Importing a very large .txt file

katiauk — Wed, 30 Dec 2015 09:47:34 GMT

@PGStats wrote:
Try adding option IGNOREDOSEOF to your infile statement.

Thansk PG, I did just that yesterday after I learned there were special characters on the txt file. It worked like a charm.

I had to get an IT guy to open the file for me because I dont have any tools to open such a big file, but when i tried to load another file and analysed the last line it imported and finding a very similar error, it was just a matter of finding what was wrong with the file.