topic Re: Why does SAS stops reading an external file after a certain field? in SAS Data Management

Why does SAS stops reading an external file after a certain field?

angeliquec — Wed, 07 Oct 2015 07:40:27 GMT

Hi all,

I have a source Excel file from our Data Management Team. I converted this file to pipe-delimited text file for import purposes.

I used the data step, infile with with the IGNOREDOSEOF statement to convert this text file to SAS. After import, I found that there was a row whose fields SAS left blank after this certain long field (appearing to be more than 32767 characters). Rows after that problematic row appear to be intact.

Perhaps a special character or a hidden character causes this occurence? I would like to know how can SAS ignore that character, and continue reading the rest of the row's data.

Re: Why does SAS stops reading an external file after a certain field?

BrunoMueller — Wed, 07 Oct 2015 07:55:17 GMT

Please post the SAS log, this will help in diagnosing the problem.

How did you convert the Excel file to the text file?

Bruno

Re: Why does SAS stops reading an external file after a certain field?

angeliquec — Wed, 07 Oct 2015 08:09:06 GMT

Hi Bruno,

I just changed the delimiter settings to pipe "|" via Control Panel, opened the Excel file, and saved it as a CSV file with the extension .txt.

Here is the SAS log. I'm converting the txt file using SAS EG.

57 %macro import_data;
58 data work.pipe212;
59 infile "&input.\&source..txt"
60 lrecl=32767
61 firstobs=2
62 encoding="WLATIN1"
63 termstr=CRLF
64 dlm='|'
65 missover
66 dsd
67 ignoredoseof;
68 input
69 %do i=1 %to &total;
70 &&field&i
71 %end;
72 ;
73 run;
74 %mend;
75 %import_data;

NOTE: The infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt" is:
Filename=E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt,
RECFM=V,LRECL=32767,
File Size (bytes)=196517682,
Last Modified=29Sep2015:16:50:58,
Create Time=29Sep2015:16:50:40

NOTE: 69312 records were read from the infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt".
3 The SAS System 16:02 Wednesday, October 7, 2015

The minimum record length was 606.
The maximum record length was 32767.
One or more lines were truncated.
NOTE: The data set WORK.PIPE212 has 69312 observations and 66 variables.
NOTE: Compressing data set WORK.PIPE212 decreased size by 96.57 percent.
Compressed is 792 pages; un-compressed would require 23104 pages.
NOTE: DATA statement used (Total process time):
real time 6.11 seconds
cpu time 4.52 seconds

Thank you,

Angelique

Re: Why does SAS stops reading an external file after a certain field?

BrunoMueller — Wed, 07 Oct 2015 08:27:02 GMT

The SAS Log says:

NOTE: 69312 records were read from the infile "E:\SAS_AC\98 SAS References\Import Pipe DLM to SAS\SOL_PIPE.txt".
3 The SAS System 16:02 Wednesday, October 7, 2015

The minimum record length was 606.
The maximum record length was 32767.
One or more lines were truncated.

I suggest you increase the value for the LRECL= option.

Bruno

Re: Why does SAS stops reading an external file after a certain field?

angeliquec — Thu, 08 Oct 2015 05:14:48 GMT

Hi Bruno,

I increased the LRECL to 100000 and got the result I was expecting. All the fields were read by SAS. Thank you!