topic Re: While importing text it read only few rows in SAS Programming

While importing text it read only few rows

Yarlanil59 — Tue, 09 May 2017 19:29:54 GMT

There is one text file in zip of about 16GB of size. tEXT FILE HAVE ABOUT 65 Millions of recoreds but it only import 6 Millions with below code and with proc Import statement alone. can suggestions please?

filename inzip ZIP "/product/users/chtrrv01/UNIVERSE1.TXT.zip";

/* Read the "members" (files) from the ZIP file */
data contents(keep=memname isFolder);
length memname $200 isFolder 8;
fid=dopen("inzip");
if fid=0 then
stop;
memcount=dnum(fid);
do i=1 to memcount;
memname=dread(fid,i);
/* check for trailing / in folder name */
isFolder = (first(reverse(trim(memname)))='/');
output;
end;
rc=dclose(fid);
run;

/* create a report of the ZIP contents */
title "Files in the ZIP file";
proc print data=contents noobs N;
run;

/* identify a temp folder in the WORK directory */
filename xl "%sysfunc(getoption(work))/KDKNW.SJ32CUIE.UNIVERSE1.TXT" ;

/* hat tip: "data _null_" on SAS-L */
data _null_;
/* using member syntax here */
infile inzip(KDKNW.SJ32CUIE.UNIVERSE1.TXT)
dlm='|' lrecl=32767 recfm=F length=length eof=eof unbuf;
file xl lrecl=32767 recfm=N;
input;
put _infile_ $varying32767. length;
return;
eof:
stop;
run;

proc import datafile=xl dbms=dlm out=account replace;
delimiter='|';

/*sheet=confirmed*/;
run;

Re: While importing text it read only few rows

Shmuel — Tue, 09 May 2017 19:52:14 GMT

Please post the full log. There may be a hint.

Please add - what SAs platform do you use?

Re: While importing text it read only few rows

Tom — Tue, 09 May 2017 19:55:32 GMT

Do you really have a text file with fixed length records of 32,767 columns?

Re: While importing text it read only few rows

Yarlanil59 — Tue, 09 May 2017 21:08:21 GMT

Thanks for your respond. Here is log

1 The SAS System 16:08 Tuesday, May 9, 2017

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=ACTIVEX;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///D:/SAS/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 filename inzip ZIP "/product/users/chtrrv01/UNIVERSE1.TXT.zip";
27
28 /* Read the "members" (files) from the ZIP file */
29 data contents(keep=memname isFolder);
30 length memname $200 isFolder 8;
31 fid=dopen("inzip");
32 if fid=0 then
33 stop;
34 memcount=dnum(fid);
35 do i=1 to memcount;
36 memname=dread(fid,i);
37 /* check for trailing / in folder name */
38 isFolder = (first(reverse(trim(memname)))='/');
39 output;
40 end;
41 rc=dclose(fid);
42 run;

43
44 /* create a report of the ZIP contents */
2 The SAS System 16:08 Tuesday, May 9, 2017

45 title "Files in the ZIP file";
46 proc print data=contents noobs N;
47 run;

48
49
50
51 /* identify a temp folder in the WORK directory */
52 filename xl "%sysfunc(getoption(work))/CHARTERC.SJ32CUIE.UNIVERSE1.TXT" ;
53
54 /* hat tip: "data _null_" on SAS-L */
55 data _null_;
56 /* using member syntax here */
57 infile inzip(CHARTERC.SJ32CUIE.UNIVERSE1.TXT)
58 dlm='|' lrecl=256 recfm=F length=length eof=eof unbuf;
59 file xl lrecl=256 recfm=N;
60 input;
61 put _infile_ $varying256. length;
62 return;
63 eof:
64 stop;
65 run;

NOTE: The infile library INZIP is:
Directory=/product/users/chtrrv01/UNIVERSE1.TXT.zip

NOTE: The infile INZIP(CHARTERC.SJ32CUIE.UNIVERSE1.TXT) is:
Filename=/product/users/chtrrv01/UNIVERSE1.TXT.zip,
Member Name=CHARTERC.SJ32CUIE.UNIVERSE1.TXT

NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file XL is:
Filename=/product/saswork/SAS_workF0A00000B57E_saspapp07/SAS_work616B0000B57E_saspapp07/CHARTERC.SJ32CUIE.UNIVERSE1.TXT,
Owner Name=v656505,Group Name=twcother,
Access Permission=-rw-r--r--,
Last Modified=09May2017:16:08:24

66
67 proc import datafile=xl dbms=dlm out=account replace;
68 delimiter='|';
69
70 /*sheet=confirmed*/;
71 run;

Re: While importing text it read only few rows

Shmuel — Tue, 09 May 2017 22:41:08 GMT

Taken from your log:

55 data _null_;
56 /* using member syntax here */
57 infile inzip(CHARTERC.SJ32CUIE.UNIVERSE1.TXT)
58 dlm='|' lrecl=256 recfm=F length=length eof=eof unbuf;
59 file xl lrecl=256 recfm=N;
60 input;
61 put _infile_ $varying256. length;
62 return;
63 eof:
64 stop;
65 run;
NOTE: The infile library INZIP is:
Directory=/product/users/chtrrv01/UNIVERSE1.TXT.zip
NOTE: The infile INZIP(CHARTERC.SJ32CUIE.UNIVERSE1.TXT) is:
Filename=/product/users/chtrrv01/UNIVERSE1.TXT.zip,
Member Name=CHARTERC.SJ32CUIE.UNIVERSE1.TXT
NOTE: UNBUFFERED is the default with RECFM=N.
NOTE: The file XL is:
Filename=/product/saswork/SAS_workF0A00000B57E_saspapp07/SAS_work616B0000B57E_saspapp07/CHARTERC.SJ32CUIE.UNIVERSE1.TXT,
Owner Name=v656505,Group Name=twcother,
Access Permission=-rw-r--r--,
Last Modified=09May2017:16:08:24
NOTE: A total of 65449514 records were read from the infile library INZIP.
NOTE: 65449514 records were read from the infile INZIP(CHARTERC.SJ32CUIE.UNIVERSE1.TXT).

You have read : 65,449,514 recodrs from the zip file to create the xl file.

Later you have:

67 proc import datafile=xl dbms=dlm out=account replace;
68 delimiter='|';
69
70 /*sheet=confirmed*/;
71 run;


NOTE: 5999999 records were read from the infile XL.
The minimum record length was 975.
The maximum record length was 3422.
NOTE: The data set WORK.ACCOUNT has 5999999 observations and 456 variables.

ending with:

Errors detected in submitted DATA step. Examine log. 5999999 rows created in WORK.ACCOUNT from XL.

...

ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors.

From your log:

Are there 2 consequtive delimiters ? Should you definne option dsd reading the file ?

Try change the line by adding this option:

infile inzip(CHARTERC.SJ32CUIE.UNIVERSE1.TXT) dlm='|' dsd lrecl=256 recfm=F length=length eof=eof unbuf; file xl lrecl=256 recfm=N;

Re: While importing text it read only few rows

Ksharp — Wed, 10 May 2017 12:39:17 GMT

Try another option.

infile inzip(KDKNW.SJ32CUIE.UNIVERSE1.TXT) ignoredoseof;

Re: While importing text it read only few rows

Yarlanil59 — Wed, 10 May 2017 14:43:29 GMT

Thanks for the reply.

I tried with

infile inzip(CHARTERC.SJ32CUIE.UNIVERSE1.TXT) dlm='|' dsd lrecl=256 recfm=F length=lengtheof=eof unbuf; file xl lrecl=256 recfm=N;

but still same error.

Errors detected in submitted DATA step. Examine log.
5999999 rows created in WORK.ACCOUNT from XL.

ERROR: Import unsuccessful. See SAS Log for details.

Re: While importing text it read only few rows

Yarlanil59 — Wed, 10 May 2017 18:10:11 GMT

Thank you @Ksharp for suggestion

I tried with

infile inzip(KDKNW.SJ32CUIE.UNIVERSE1.TXT) ignoredoseof;

But same error as before.

Re: While importing text it read only few rows

Tom — Wed, 10 May 2017 18:23:38 GMT

Can you provide more information?

What operating system is SAS running on?

Can you use PIPE to run an external ZIP processing program so that you can avoid having to the use ZIP engine from SAS?

It looks like you are trying to do a binary copy from the file in the ZIP file to a text file and so you had RECFM=F, but then you also added DLM='|' which should not apply to a binary file. Why not just read the file as a text file to begin with?

filename xl "%sysfunc(getoption(work))/KDKNW.SJ32CUIE.UNIVERSE1.TXT" ;

data _null_;
  infile inzip(KDKNW.SJ32CUIE.UNIVERSE1.TXT) lrecl=32767 ;
  file xl lrecl=32767;
  input;
  put _infile_;
run;

Why are you using PROC IMPORT to read a delimited file? You can write a better program if you know what is in the file. PROC IMPORT can only guess at how to read the file.

If your file really is a ZIP file with just one text file in it then what I normally do when running on Unix is to let the unzip command just pull the file to the standard output and read it from there.

data want ;
  infile 'unzip -p myfile.zip' pipe firstobs=2 dsd dlm='|' truncover ;
  length var1-var20 $200 ;
  input var1-var20;
run;