topic Re: Use infile to load large CSV file, but 0 records read from file in SAS Programming

Use infile to load large CSV file, but 0 records read from file

SAS_usercanada — Fri, 05 Jul 2024 18:54:58 GMT

%macro infile;

%Do Yr .= &strYr %To EndYr;

Filename caseA "&sourcefile.\caseA_&Yr..CSV";

Data temp. caseA_&Yr.;

length

Filr_number $ 15

Rtn_cd $4

... ;

infile casA Firstobs = 2 DSD Termstr = CRLF DLM =',' LRECL=32767 Missover;

input

Filr_number $

Rtn_cd $

...;

informat

Filr_number $CHAR15.

Rtn_cd $CHAR1.

...;

format

Filr_number $CHAR15.

Rtn_cd $CHAR1.

...;

run;

%END;

%Mend;

%infile;

NOTE: The infile CaseA is:
Filename=E:\Master file\CaseA_2020.CSV,
RECFM=V,LRECL=32767,
File Size (bytes)=8571503098,
Last Modified=May 29, 2024 14:16:42,
Create Time=May 29, 2024 14:16:42

NOTE: 0 records were read from the infile CaseA.
NOTE: The data set temp.CaseA_2020 has 0 observations and 120 variables.

Re: Use infile to load large CSV file, but 0 records read from file

Reeza — Fri, 05 Jul 2024 19:31:08 GMT

Your filename is casEa and your infile is CasA (missing E) but could be a typo here?

Can you add the macro debugging options and post the full log?

Also, make sure the file has the data expected (open the CSV file or use a basic input to read a few lines).

Macro debugging options

options mprint mlogic;

Check the actual file contents:

data _null_;
infile 'path to csv file no macro variables.csv' dsd firstobs=2 dsd termstr = crlf dlm=',' lrecl=32767 missover;

input;

put _infile_;
run;

@SAS_usercanada wrote:

%macro infile;

%Do Yr .= &strYr %To EndYr;

Filename caseA "&sourcefile.\caseA_&Yr..CSV";

Data temp. caseA_&Yr.;

length

Filr_number $ 15

Rtn_cd $4

... ;

infile casA Firstobs = 2 DSD Termstr = CRLF DLM =',' LRECL=32767 Missover;

input

Filr_number $

Rtn_cd $

...;

informat

Filr_number $CHAR15.

Rtn_cd $CHAR1.

...;

format

Filr_number $CHAR15.

Rtn_cd $CHAR1.

...;

run;

%END;

%Mend;

%infile;

NOTE: The infile CaseA is:
Filename=E:\Master file\CaseA_2020.CSV,
RECFM=V,LRECL=32767,
File Size (bytes)=8571503098,
Last Modified=May 29, 2024 14:16:42,
Create Time=May 29, 2024 14:16:42

NOTE: 0 records were read from the infile CaseA.
NOTE: The data set temp.CaseA_2020 has 0 observations and 120 variables.

Re: Use infile to load large CSV file, but 0 records read from file

SAS_usercanada — Fri, 05 Jul 2024 19:40:19 GMT

It is my typo here.

Re: Use infile to load large CSV file, but 0 records read from file

Tom — Fri, 05 Jul 2024 19:46:07 GMT

So from your SAS log we have the following information:

File Size (bytes)=8571503098
NOTE: 0 records were read from the infile CaseA.

And since your INFILE statement said to start reading on the second observation that means that all 8,571,503,098 bytes are part of the first observation.

Are you sure this is a CSV (which is a TEXT file)? Are you sure the CSV file is using CR+LF ('0D0A'x) to mark the end of the lines?

How long to you expect each line to be? Try reading a little more than that many bytes from the file and see if you tell whether it is a text file and if so what character(s) are being used to mark the end of the lines of text. So if you expect the header line to be 400 characters and the first data line to be 300 characters you might try reading in just the first 1000 characters and looking to see if there are any CR or LF characters in there.

data _null_;
  infile 'E:\Master file\CaseA_2020.CSV' lrecl=100 recfm=f obs=10;
  input;
  list;
run;

Perhaps the file is from a Unix machine so it only use LF as the end of line character. Change the TERMSTR= option. PS It is almost never the case that you want the behavior of the ancient MISSOVER option. Usually you will want the behavior of the new (probably only 30+ years old by now) TRUNCOVER option instead.

infile casA Firstobs = 2 DSD Termstr = LF DLM =',' LRECL=32767 TRUNCOVER;

If someone accidentally let Excel on a Mac create the CSV file without paying attention to what file format it was going to use it might have been created using CR only as the end of line marker. (Somehow Excel never learned that MacOS is now a UNIX version and should be use LF as the end of line character.) If that is the case then add the TERMSTR=CR option to the INFILE statement.

infile casA Firstobs = 2 DSD Termstr = CR DLM =',' LRECL=32767 TRUNCOVER;

Re: Use infile to load large CSV file, but 0 records read from file

Kurt_Bremser — Fri, 05 Jul 2024 19:48:10 GMT

Your TERMSTR is probably wrong, try LF (UNIX) or CR (Max) instead.